Be the first user to complete this post
|
Add to List |
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:
- VBA-Excel: Create or Add Worksheets at the Run time.
- VBA-Excel: Convert Numbers (Rupees) into Words OR Text - Updated Till 1000000 Crore With Decimal Numbers
- VBA-Excel — AttachmentFetcher — Download all the Attachments from All the Mails of Specific Subject in Microsoft Outlook .
- VBA-Excel: Create a new Word Document
- VBA-Excel: Read XML by Looping through Nodes