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:

[sourcecode language=”VB”]
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

[/sourcecode]

Send Mail With Multiple Different Attachments From MS Outlook using Excel

Send Mail With Multiple Different Attachments From MS Outlook using Excel

Send Mail With Multiple Different Attachments From MS Outlook using Excel-1

Send Mail With Multiple Different Attachments From MS Outlook using Excel-1

You may also like...

2 Responses

  1. Drake says:

    You are a rockstar – this helped me fix an attachment issue I was having for which I did almost 4 hours of research to try and fix!

Leave a Reply to SJ Cancel reply

Your email address will not be published. Required fields are marked *

Show Buttons
Hide Buttons
%d bloggers like this: