CODEWITHSUNDEEP
excelvbaoutlook
Acidgemcutter Agc
Acidgemcutter Agc

Reputation: 47

Add attachment with varying date in file name to Outlook mail

I have an Excel file named "Home Audio for Planning (28-3-2013).

The date will change every day but the text will be the same.

How do I attach those files to Outlook?

Sub Test()

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hello World!"

        .Attachments.Add ("C:\Users\Desktop\Today\Home Audio for Planning   (28-3-2013).xlsx")

        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Upvotes: 1

Views: 76777

Answers (5)

Gajendra Santosh
Gajendra Santosh

Reputation: 159

With OutMail
    .To = ""
    .BodyFormat = olFormatHTML  '---Default
    .Attachments.Add ("C:\Users\Desktop\Test.txt")
    .Display
End With

If not.BodyFormat = olFormatHTMLfile will be attached in the mail body

Upvotes: 0

user9497845
user9497845

Reputation: 1

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

Dim strSubject As String
Dim StrSub As Integer
Dim AttachCnt As Integer


AttachCnt = Item.Attachments.Count
strSubject = Item.Subject
StrSub = Len(strSubject)
strBody = Item.Body
strBod = InStr(1, UCase(strBody), "ATTACH")
cnsolidateMsg = ""

If strBod <> 0 And AttachCnt = 0 Then
        cnsolidateMsg = cnsolidateMsg & "Attachment is Null." & vbNewLine
End If

If StrSub = 0 Then
    cnsolidateMsg = cnsolidateMsg & "Subject is Empty." & vbNewLine
End If
If UCase(Trim(strSubject)) = "FW:" Then
    cnsolidateMsg = cnsolidateMsg & "Forward mail subject is empty." & vbNewLine
End If
If UCase(Trim(strSubject)) = "RE:" Then
    cnsolidateMsg = cnsolidateMsg & "Reply mail subject is empty." & vbNewLine
End If

If cnsolidateMsg <> Empty Then
    If MsgBox(cnsolidateMsg & vbNewLine & "Are you sure you want to send the Mail?", vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check for send mail") = vbNo Then
        Cancel = True
    End If
End If

End Sub

Upvotes: 0

Luigi
Luigi

Reputation: 486

Easy,

  .Attachments.Add ("C:\Users\Desktop\Today\Home Audio for Planning (" & FORMAT(DATE,DD-MM-YYYY)")

Upvotes: 1

user2063626
user2063626

Reputation:

Try below code : strLocation will be generated dynamically. You can just pass this variable to your attachments. File name generated would be like Home Audio for Planning_28-03-2013.xlsx

Sub Test()
    Dim strLocation As String

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hello World!"

        strLocation = "C:\Users\Desktop\Today\Home Audio for Planning" & Format(Now(), "_DD-MM-YYYY") & ".xlsx"
        .Attachments.Add (strLocation)
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Upvotes: 7

GoaKoala
GoaKoala

Reputation: 116

Did you try to change the attachemnt name dynamic. For ex;

.Attachments.Add ("C:\Users\Desktop\Today\Home Audio for Planning   (" + timeVariable  + ").xlsx")

and you can set the time variable before to match the date of the date in the required format.

Cheers

Upvotes: 0

Related Questions