Results 1 to 10 of 10

Thread: How to send PDF attachment using VBA to multiple mail recipients?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Smile How to send PDF attachment using VBA to multiple mail recipients?

    Hi All,

    Hope you are doing good,

    I want to send a PDF attachment to multiple email recipients (approx 50 people) with same attachment. I want the recipeint name to me customized as per user (example Hi Maria) and also Subject line should have recipients Company name mentioned.

    I am not so good with VB, and wish to ask is it possible to send mass email to different recipients with same attachment?

    Any form of help is highly appreciated and do let me know if more information is needed.

    Regards
    Mitul

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    is it possible to send mass email to different recipients with same attachment?
    yes of course
    do let me know if more information is needed.
    how are you storing your data (company names and personalization)? database, workbook, document or other?

    I am not so good with VB, and wish to ask is it possible to send mass email to different recipients with same attachment?
    this is quite possible, but is not what you are wanting to do, you want to send single email to each recipient with same attachment

    there are 2 main choices for sending emails cdo.message included in most windows installations, or outlook, which requires microsoft outlook to be installed
    there are many examples for each in this forum, also there are some other choices, personally i use CDO, though i have posted code before to use outlook

    many smtp servers limit the number of outgoing emails that can be sent in a single batch, that may require some error handling
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    Quote Originally Posted by westconn1 View Post
    yes of course
    how are you storing your data (company names and personalization)? database, workbook, document or other?

    this is quite possible, but is not what you are wanting to do, you want to send single email to each recipient with same attachment

    there are 2 main choices for sending emails cdo.message included in most windows installations, or outlook, which requires microsoft outlook to be installed
    there are many examples for each in this forum, also there are some other choices, personally i use CDO, though i have posted code before to use outlook

    many smtp servers limit the number of outgoing emails that can be sent in a single batch, that may require some error handling

    Actually I just want to send a email to stake holders with a same attachment and email body. Just want to change the first name of recipient and in Subject just one word will be changed per email.
    I have Microsoft outlook installed and I have to use that.
    Data is saved on local machine.
    Also if you have some available code available I would highly appreciate it if you could share the same with me.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    as you did not specify, this example gets data from a workbook
    Code:
    Set sht = Sheets("sheet1")     ' change to suit
    Set myOutlook = GetObject(, "Outlook.Application")
      pdffile = "c:\temp\somefile.pdf"
    rw = 1    ' change to 2 if you have a header row
    Do Until IsEmpty(sht.Cells(rw, 2))
               Set myMessage = myOutlook.CreateItem(0)
                With myMessage
                  .To = sht.Cells(rw, 2)  ' assumes email address is in column B
                  .Subject = "Reports for " & sht.cells(rw,1)  ' assumes name is in column A, change this to to whatever you want to say
                  .body = "Please find attached pdf"  ' and this if required
                  .attachments.add pdffile
                  .send
              End With
         rw = rw + 1
    Loop
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    Quote Originally Posted by westconn1 View Post
    as you did not specify, this example gets data from a workbook

    My apologies, I could not get you in first go. What information you require?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    how are you storing your data (company names and personalization)? database, workbook, document or other?
    as i asked previously
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    Quote Originally Posted by westconn1 View Post
    as i asked previously
    I am sorry for missing it.

    I have prepared and stored in my local machine in from of Excel file
    data is email ID and names of recipients, body of email and also names of their company.
    I have to write separately same email to each of them with same attachment and email content.

    only difference would be recipients name and two words would change in subject line (company name & region code) per email

    Hope this information helps, I thank you in advance for your kind help and time

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    the sample i posted is written to be used in an Excel workbook, automates outlook to send the emails, this could be changed to run from outlook and automate excel, or run in a .vbs script and automate both outlook and excel

    outlook needs to already be open for any code to work

    to many choices?

    you should be able to figure out what you need to change to make the above sample match your data, just change the column number to put the correct data at the appropriate position, see the comments in the code for the assumptions i made about data position

    the sample sends email to every row of data, stops at first empty row
    for testing purposes make sure to send all the emails to yourself
    Code:
    .to = "me@myemail.com"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    I am sorry for replying late, got stuck with somethings.
    the code runs well with some tweaks
    I wish to ask you if there is a way I could make small changes to body of letter
    Like I want to add Hi Anna for one mail then Hi Bella for another one

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to send PDF attachment using VBA to multiple mail recipients?

    no problem
    Code:
                With myMessage
                  .To = sht.Cells(rw, 2)  ' assumes email address is in column B
                  .Subject = "Reports for " & sht.cells(rw,1)  ' assumes name is in column A, change this to to whatever you want to say
                  .body = "Hi " & sht.cells(rw, 3) & vbnewline "Please find attached pdf"  ' change column number to suit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width