Macro to send email with multiple attachment
hi every one,
I have the below macro which goes onto outlook and works very well, however, it has one limitation that i can send only one file as an attachment. I was wondering if it is possible to add multiple attachments instead of just one attachment per email. every thing else in the code is fine, just to add additional code to add multiple attachments. for example, in the below macro, outlook picks up information from cell a, cell b and cell c in an excel file containing file name, email address of recipient and file path. Now is it possible that multiple file names can be put in cell A to attach to a single email? or any other way to do this. Because I have list of clients to whom i send multiple files to each of them. The below macro only allows me to send one file in one email and i have to send each client multiple emails for each attachment.
please help me in this as this will really solve my problem if the below code can be modified to include multiple attachments.
thanks and best regards,
CJ
[VBA]
Sub ReadExcel()
Dim ExcelObject As Object
Dim OutlookApp As Outlook.Application
Dim NewMessage As Outlook.MailItem
Dim OutlookNamespace As Outlook.NameSpace
Dim fName, fLoc, eAddress As String
Dim fNameAddress, fLocAddress, eAddressAddress As String
' Set up the spreadsheet you want to read
On Error Resume Next
Set ExcelObject = GetObject(, "Excel.Application")
If Not Err.Number = 0 Then
MsgBox "You need to have Excel running with the appropriate spreadsheet open first", vbCritical, "Excel Not Running"
End
End If
' Read in the data and create a new message with attachment for each Excel entry
CellRow = 1
Set OutlookApp = Outlook.Application
Do Until ExcelObject.Range(fNameAddress) = ""
fNameAddress = "A" & CellRow
eAddressAddress = "B" & CellRow
fLocAddress = "C" & CellRow
fName = ExcelObject.Range(fNameAddress)
fLoc = ExcelObject.Range(fLocAddress)
eAddress = ExcelObject.Range(eAddressAddress)
fName = fLoc & "\" & fName
Set OutlookApp = Outlook.Application
Set NewMessage = OutlookApp.CreateItem(olMailItem)
Set myAttachments = NewMessage.Attachments
myAttachments.Add fName
With NewMessage
.Recipients.Add eAddress
.Attachments = fName
.Display
' .Subject = "Put your subject here"
' .Send
End With
CellRow = CellRow + 1
fNameAddress = "A" & CellRow
Loop
End Sub
[/VBA]
just a small amendment to the code
Hi Zack,
firstly, thank you so much for this amazing code, i tried it and it works really well. thanks alot really.
i am having only one issue with the code now after trying it for two days. when i run the code, it looks for file name defined in column a in locaion defined in location c. now if the file does not exist in the specified location, the code stops with error and does not complete the process for the rest of the rows.
what I am looking for is to amend the code so that in case out of all the file names mentioned in column a , if it does not find the file specified in column a, it just skips that file and move on to search and pick teh next file and complete the process till the end for all the rows. it may give a message only that the file does not exist but the code continues till the end.
Pleaseeee look into this and see if it is possible to amend the code a bit.
thanks again for all the help and for your effort on this.
Best regards,
CJ
1 Attachment(s)
thanks a lot, another catch...
Dear Zack,
thank you so much for modifying the code. Now the last issue i am facing is that when I run the macro with the Newmessage.send in outlook, it gives an error that "outlook does not recognize one or more names." and the process stops at the very start without moving to teh next email.
Is there any way to make outlook send the email without checking the address book of the validity of the email address? What i think is causing this is because the macro tries to send the email immediately while outlook takes a couple to seconds to recognize the email address, can you please solve this issue?
i have attached the screenshot of the error that i am getting. please check the possibility as I still have to send the email manually. Outlook still attaches the files but i disable the newmessage.send option and then manually click send to all the emails attachments generated by the macro. by the time i go to each email, outlook has already recognized the email addresses.
Thanks a lot Zack for your help on this, please check if what i am asking can be done.
best regards,
CJ