Emailing Multiple attachments with VBA code

Puranichappal

New Member
Joined
Jun 16, 2017
Messages
1
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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 [COLOR=#333333]Sheets("Data").Range("B4").Value & "" & Sheets("Data").Range("A" & i).Value & ".xlsx"[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,153
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top