Email with attachment using VBA

[VARGEESH]

Hi,


I have 200 pdf documents and the name of the documents is from 1 to 200 in a folder(Names of the docements like 1, 2 , 3, ... 200)


I need to send each of the documents in 200 separate emails to the same receipient with the same message body but with the different attachment.


Can you please advice me to do this activity using VBA.


Regards,

Vargeesh

 

[Luke M]

You should be able to adapt the method describe here:

http://www.rondebruin.nl/mail/folder2/files.htm


Ron's method loops through names and sends the files. You could use 200 rows with the same name/email and just change the file name. Here's a formula to give you an example of how to easily build the file path:

="C:My DocumentsFiles To Send" & ROW(A1) & ".pdf"

 

[VARGEESH]

Thank you so much Luke!


It works well.


And I need to add "CC" in my email.


Please advise me.


Thanks!

 

[Luke M]

In the code where it starts to build the message, modify to something like

[pre]

With OutMail
.To = cell.Value
.CC = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, 2).Value
'etc

[/pre]
Then, put the CC address in col D.

 

[VARGEESH]

Hi Luke,


Now I am able to send the emails using excel.


Thanks for your kind advise.


And one thing, when I send the emails I need to send from a different account. Which means from generic email Id. So, I have to change the "From" address when there is a need.


I tried with the below code

[pre]

With OutMail
.From = cell.Offset(0,-1).Value

[/pre]

But it was not working.


Please advise me.


Thanks!

 

[Luke M]

Hmm, I've never changed the "From" field. Not completely sure if it's possible, but check out the discussion here:

http://visualbasic.ittoolbox.com/groups/technical-functional/visualbasic-l/excel-vba-and-outlook-how-to-change-the-from-and-send-replies-to-email-addresses-2727943

 

[VARGEESH]

Luke it works well!


To change the from address:

.SentOnBehalfOfName = abc@abc.com


My code is:


[code]With OutMail

.SentOnBehalfOfName = cell.Offset(0, -1).Value[/code]


Thank you so much for your assistance from flower to fruit.


With Thanks,

Vargeesh M

 

[Luke M]

Sweet, glad it got solved, and thanks for the feedback. =)