Send Mail With Multiple Different Attachments From MS Outlook using Excel.

In earlier post you have learned how to send Excel Workbook as attachment with email from MS Outlook using Excel. In this article we will learn about how to Send Mail With Multiple Different Attachments From MS Outlook using Excel. These attachments might be anything, an image, word document, excel document, pdf file, text file etc.

Note: Don't provide any filter in GetOpenFileName() to select any file as attachment.

strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open")

I would recommend that you must read Send a Simple Mail From MS Outlook Using Excel and how to send Excel Workbook as attachment with email from MS Outlook using Excel to understand the complete code below.

Complete Code:

Dim mainWB As Workbook
Sub sumit()
Dim SendID
Dim CCID
Dim Subject
Dim Body
Dim AttachFile
Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set Doc = olMail.GetInspector.WordEditor
Set mainWB = ActiveWorkbook

SendID = mainWB.Sheets("Mail").Range("B1").Value
CCID = mainWB.Sheets("Mail").Range("B2").Value
Subject = mainWB.Sheets("Mail").Range("B3").Value
Body = mainWB.Sheets("Mail").Range("B5").Value
AttachFile = mainWB.Sheets("Mail").Range("B4").Value
With olMail
.to = SendID
If CCID <> "" Then
.CC = CCID
End If
.Subject = Subject
mainWB.Sheets("Mail").Range("B9").Copy
Set WrdRng = Doc.Range
.Display
WrdRng.Paste
For i = 4 To 8
atch = mainWB.Sheets("Mail").Range("B" & i).Value
If atch <> "" Then
.Attachments.Add atch
End If
Next
.Send

End With
MsgBox ("you Mail has been sent to " & SendID)
End Sub
Function browse(rng)
Dim FSO As Object
Dim blnOpen
Dim mainWB As Workbook
Set mainWB = ActiveWorkbook
strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Function
Else
mainWB.Sheets("Mail").Range(rng).Value = strFileToOpen
End If
End Function
Sub browse1()
browse ("B4")
End Sub
Sub browse2()
browse ("B5")
End Sub
Sub browse3()
browse ("B6")
End Sub
Sub browse4()
browse ("B7")
End Sub
Sub browse5()
browse ("B8")
End Sub

Also Read:

1. VBA-Excel: Writing Text to Word document
2. VBA-Excel: Open an Existing Word Document
3. VBA-Excel: Add/Insert a Image/Picture in Word Document
4. VBA-Excel: Read Data from XML File
5. VBA-Excel: Enumerate all the opened word document