Email with Multiple Attachments through VBA [Solved/Closed]

Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
-
Dear All,

I need to send the multiple emails via Outlook through VBA.

Column A Row 2 = Name of Person, Which must be Displayed on .Body
Column B Row 2 = .To Email Address (It could be multiple)
Column C Row 2 = .CC Email Address (It could be multiple)
Column D Row 2 = .Subject
Column E Row 2 = .Attachments.Add (It could be multiple)

Sheet2 is the Matter which must be copy and paste to the .body

I need looping Macro to send the emails one by one with attachments or attachment with the formatted body which is located in Sheet2.


Note: Sheet2 Matter will remain same for all rows.

Thanks in Advance.

Looking forward to your usual co-operation.

Regards,
Game Start Now
See more 

2 replies

Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1532
0
Thank you
Well, where are stuck? The mail object? The formatting of the email structure itself? I will not! For certain, will not provide a mailing program turn key. Let us know where you are stuck!
Game Start Now
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6 -
It doesn't stuck anywhere. I am in need of such Macro.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1532 -
Sorry, macros cant build and instantiate a mail object! It cannot be done with a macro!

You must include a package on load, and reference it to make it work!
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
0
Thank you
Below code works fine but it allow only one attachment.
I need the macro to add attachments from column H to column Z. If one path is in column H and another path is in column I.

Sub Send_Email()

Sheets("Sheet1").Activate

Range("A3:J55").Select
Application.CutCopyMode = False

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
Sheets("Sheet2").Select
MYROW = Range("G10000").End(xlUp).Offset(1, 0).Row
.Item.To = Range("D" & MYROW).Value
.Item.CC = Range("E" & MYROW).Value
.Item.Subject = Range("F" & MYROW).Value
.Item.Attachments.Add Range("H" & MYROW).Value
Sheets("Sheet1").Select
.Item.Send
End With


End Sub
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1532 -
Make an array of the objects, then loop through the array!

Check this out: https://msdn.microsoft.com/en-us/library/wak0wfyt.aspx
Game Start Now
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6 > ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
I really don't know how to make it.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1532 -
You aren't even looping through to Z in the above code. If you cannot build a simple loop, then you need to look into loops.
Game Start Now
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6 > ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
If you can do then why don't you help me in this instead of talking.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1532 -
YOU will learn more if you understand what YOU are doing, not just cutting and pasting what I already understand!