Results 1 to 2 of 2

Thread: Emailing Multiple attachments with VBA code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Emailing Multiple attachments with VBA code

    Hi,

    I am using VBA code to send multiple clients with copy to some.

    It looks like below... I want to attach multiple file ....plz suggest.

    **************************************************

    Sub Mail_Send_withattachment()
    On Error Resume Next
    Dim employeename, tomailid, ccid, bccid, compname, subject, strbody, strbody1, Attachfile As String
    'Don't forget to set a reference to Outlook in the VBA editor
    Dim OutApp As Object
    Dim OutMail As Object

    Application.DisplayAlerts = True

    i = 6
    Sheets("Data").Select
    While Sheets("Data").Range("A" & i).Value <> 0
    employeename = Sheets("Data").Range("B" & i).Value
    tomailid = Sheets("Data").Range("C" & i).Value
    ccid = Sheets("Data").Range("D" & i).Value
    'ccid = Sheets("Data").Range("B2").Value
    bccid = Sheets("Data").Range("B3").Value
    subject = Sheets("Data").Range("B1").Value
    Attachfile = Sheets("Data").Range("B4").Value & "" & Sheets("Data").Range("A" & i).Value & ".xlsx"

    strbody = "Dear " & "<b>" & employeename & " (" & Sheets("Data").Range("A" & i).Value & ")" & " ,</b><br>" & Sheets("Msg Data").Range("mainmsg").Value & "</br>"


    strbody1 = "<br>" & Sheets("Msg Data").Range("signature").Value & "</br>"

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    With OutMail
    .To = tomailid
    .CC = ccid
    .BCC = bccid
    .subject = subject
    .Attachments.Add Attachfile
    .HTMLBody = strbody & strbody1
    '.Display
    .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

    Sheets("Data").Select
    Sheets("Data").Range("E" & i).Value = "=TEXT(NOW(),""DD-MMM-YY hh:mm:ss am/pm"")"
    Sheets("Data").Range("E" & i).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False


    i = i + 1


    Wend


    End Sub

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    396
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Emailing Multiple attachments with VBA code

    Just add in more
    Code:
    .Attachments.Add
    lines under the current one in your code. You can either add new variables to hold the file path or just reference it direct

    Code:
    .Attachments.Add  "C:\Myfile.txt"
    or
    
    .Attachments.Add Sheets("Data").Range("B4").Value & "" & Sheets("Data").Range("A" & i).Value & ".xlsx"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •