Code for outlook mail attachment

[Veeru106]

Hi,



I have one code where I want to send outlook mail.



Code is working fine except I can sent only one attachment. (Provided in path)



Below code is useful because it allows me to send 50 emails at a time (I need to create separate sheet and include every person name to whom I want to send mail)



Can anyone modify this code to include 2 or more than 2 attachments?

Sub Mail_ActiveSheet()

Dim outapp As Object, cl As Range, bodytekst As Variant, outmail As Object, strlocation As String

strlocation = "C:\Users\vv\Documents\Automation Work\Daily File\Daily File Report - 06-01-2017.xlsm"





With CreateObject("Outlook.Application").createitem(0)

For Each cl In ThisWorkbook.Sheets("Distribution List").Columns(2).SpecialCells(2)

If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 1).Value) = "yes" Then

If strto = "" Then strto = stro & ";"

strto = strto & cl.Value & ";"

End If

Next cl

For Each cl In ThisWorkbook.Sheets("Distribution List").Columns(2).SpecialCells(2)

If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 2).Value) = "yes" Then

If strcc = "" Then strcc = strcc & ";"

strcc = strcc & cl.Value & ";"

End If

Next cl

.To = strto

.CC = strcc

.BCC = ""

.Subject = "AGI US Retail Daily Focus Fund Sales - as of "

.Body = "** Please see bottom of each page for list of data items included and excluded in attached report"

.Attachments.Add (strlocation)

.display

End With

MsgBox "Your Mail is ready"



End Sub

Thanks

 

[Deepak]

This might helped you...

http://forum.chandoo.org/threads/outlook-mail-with-multiple-attachments.34667

 

[Veeru106]

Yes it is great but limitation of this code is that I can use only one email id as cc, where as I want more than 40 email ids. Thanks

 

[Deepak]

Some confusion..
Initially asking for attachment latter groping of email id.

 

[Veeru106]

Sorry for the confusion....actually i want both the things....above code mentioned by me gives me option to send mail to multiple email ids but only one attachment,,,,where as code shared by you in forum gives me option to send multiple attachments but with one email ids....i want combination of both...multiple attachments plus multiple emails ids...Thanks

 

[Veeru106]

Hi Deepak,

Do we have any progress on the same...or may be we have a new code altogether....thanks

 

[dan_l]

just implement this somewhere:

For Each cl In ThisWorkbook.Sheets("Distribution List").Columns(2).SpecialCells(2)

If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 1).Value) = "yes" Then

If strto = "" Then strto = stro & ";"

strto = strto & cl.Value & ";"

End If

Next cl

For Each cl In ThisWorkbook.Sheets("Distribution List").Columns(2).SpecialCells(2)

If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 2).Value) = "yes" Then

If strcc = "" Then strcc = strcc & ";"

strcc = strcc & cl.Value & ";"

End If

Next cl

there are better ways of handling that, fwiw

 

[Chihiro]

Personally, I'd just set up distribution list on mail client/email server side. Instead of CCing individual emails.

Less hustle and will result in cleaner code. Not to mention, you could have everyone within organization use same distribution list.

 

[Veeru106]

Thanks for Replying.....code is working though...thanks