Mails with attachments through vba macro

Question

I am running a macro for sending mails to multiple recipients via Outlook with one or more attachments through vba excel. I am not well versed in macros and hence took some inputs from various sources and came upon the below final code.

However I have mentioned max. limit of 3 file attachments which is constant for all recipients but have to disable by commenting whenever I have to attach only 1 or 2 files accordingly like e.g in the below code I have disabled the 2nd and 3rd attachment columns for attaching 1 file across. Is there any way where the macro would automatically take the inputs according to the values entered and left blank e.g If one recipient has 1 attachment and the next recipient has 2 or 3 attachments

Sub SendMail() Dim objOutlook As Object Dim objMail As Object Dim ws As Worksheet Set objOutlook = CreateObject("Outlook.Application") Set ws = ActiveSheet For Each cell In ws.Range("A2:A1000") Set objMail = objOutlook.CreateItem(0) With objMail .To = cell.Value .Cc = cell.Offset(0, 1).Value .Bcc = cell.Offset(0, 2).Value .Subject = cell.Offset(0, 3).Value .Body = cell.Offset(0, 4).Value .Attachments.Add cell.Offset(0, 5).Value '.Attachments.Add cell.Offset(0, 6).Value '.Attachments.Add cell.Offset(0, 7).Value .Send End With Set objMail = Nothing Next cell Set ws = Nothing Set objOutlook = Nothing End Sub

You may want to add some wait time, a second or two, after sending a mail. Sometimes it's too fast for outlook to send so many mails. — Egan Wolf
– Egan Wolf, Commented Jan 10, 2017 at 8:00
@EganWolf - Well I haven't faced any issue as such regarding time to send. Takes hardly 2 secs to mail 40 recipients. Let me know if I need to add something? — Irvin
– Irvin, Commented Jan 10, 2017 at 9:44
Actually, I have never tested that. I was using and updating a macro written by somebody else that was sending about 30 mails with attachment and there was a comment, that sometimes error occurs without sleep time. — Egan Wolf
– Egan Wolf, Commented Jan 10, 2017 at 10:00

Tim Williams · Accepted Answer · 2017-01-10 07:43:38Z

1

Dim i As Long, c As Range '.... With objMail .To = cell.Value .Cc = cell.Offset(0, 1).Value .Bcc = cell.Offset(0, 2).Value .Subject = cell.Offset(0, 3).Value .Body = cell.Offset(0, 4).Value For i = 5 to 6 Set c = cell.Offset(0, i) If c.Value <> "" Then .Attachments.Add c.Value Next i .Send End With '....

edited Jan 10, 2017 at 7:43

answered Jan 10, 2017 at 7:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Egan Wolf Over a year ago

You used With inside With and I'm quite sure that VBA will not know how to read .Attachments.Add .Value.

Irvin Over a year ago

Works great! Thank you @TimWilliams, Thanks a lot both for your inputs. Well a tiny issue - The mails get sent before encountering an error Microsoft Visual Basic Run-time error '-214746729 (80004005)': We need to know who to send this to. Make sure you enter at least one name.

Irvin Over a year ago

Figured it out, has something to do with MS Office version updates. Thanks again for the solution. Cheers, have a great day :)

Collectives™ on Stack Overflow

Mails with attachments through vba macro

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related