Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51

    Thumbs up MS Access 2003 Mail Merge and Mailing from Outlook

    Hello All,



    I have a process of mass mailing to customers.I have a fixed body of letter which goes in my email (outlook 2003) along with a pdf attachment personalized to each customer.

    Is there a way to email from access(2003) through Outlook(2003) with the pdf attachment. Please note the pdf attachment is personalized so when i email X customer the pdf address to X should come . I name the pdf with the customer code and the same code and email is available in access table also.

    I am exploring an option for this mass mailing and also want to know how i can do mailmerge using Access presently my excel holds data and i merge it in word.

    Appreciate your help

    Regards

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I do pretty much the same thing. I would use this for the PDF:

    http://www.lebans.com/reporttopdf.htm

    and here are some thoughts on the emailing:

    Emailing a different report to each recipient
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    I downloaded the access file with the DLL files from the first link I guess it can open only SNP files how do i convert a word file to pdf....

    On the second link for mailing individual links it refers to code i am not good at modules or VBA how do I do this should I copy the code present in the link to a module ?

    Thanks

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure how to send the Word doc to PDF; I always have the content in Access.

    I would study the code to understand what it's doing. Then you can cut/paste the appropriate parts into your code. Feel free to ask if you can't figure out what any particular bit is doing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    I think thats a good idea to keep the letter content in Access through forms ...that is what i tried after reading the link which you provided. I get one error "Frmmain" is missing and it points out to the line in the module (If (IsNull(Forms!frmMail!CCAddress)) Then).

    But this Form is very much present but not sure why it should hive this error. below is the VBA code. ofcourse after the End sub i did a Ctrl G anfor specifing the attachment but i got the above error

    Option Compare Database

    Option Compare Database
    Option Explicit

    Sub SendMessages(Optional AttachmentPath)

    Dim MyDB As Database
    Dim MyRS As Recordset
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim TheAddress As String

    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset("tblMailingList")
    MyRS.MoveFirst

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    Do Until MyRS.EOF
    ' Create the e-mail message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = MyRS![EmailAddress]

    With objOutlookMsg
    ' Add the To recipients to the e-mail message.
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo

    ' Add the Cc recipients to the e-mail message.
    If (IsNull(Forms!frmMail!CCAddress)) Then
    Else
    Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, the Body, and the Importance of the e-mail message.
    .Subject = Forms!frmMail!Subject
    .Body = Forms!frmMail!MainText
    .Importance = olImportanceHigh 'High importance

    'Add the attachment to the e-mail message.
    If Not IsMissing(AttachmentPath) Then
    Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

    ' Resolve the name of each Recipient.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the form open when the code runs? It needs to be. You mentioned frmMain but I see frmMail in the code. Might be a typo, but make sure the spelling is correct.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    Yes I had kept the form open when i ran the code. Sorry it was a typo here but i its frmmail in the access file.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the form is open and the form and control names are spelled correctly, I'm puzzled. Can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51

    Re

    Hi

    I had attached the access file and the empty test text file.

    One more question I have is i had ket the form open and i filled the below infor

    CCaddress - Dear Customer

    Subject - some sub text

    Main message - a para

    after i close this form and reopen none of this info is saved i understand its not linked to a table ...am i making a mistake .

    In the point 5 in link http://support.microsoft.com/?id=318881

    form creation i saw RecordSource: tblMailingList i dint do anything for this may be is this the error ?

    Thanks again for your help

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The textbox is not named "CCAddress", it's named "Text1", so the line has to be:

    If (IsNull(Forms!frmMail.Text1)) Then

    CCAddress is just the caption of the associated label. Same for the other two. If the form is not bound to a table, anything entered into the textboxes will be lost when the form is closed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    Now i am getting the below line as error in yellow

    Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)

    will it be possible for you to fix my db with appr txt in the form and send me ....

    Thanks

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm actually not where I have your db working right now. It doesn't appear you changed to the actual textbox name there. You have to do that in all instances where you refer to the form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    Finally got it now one more request ...if i want to mask the sender email ID or make it as not repliable by receipient..how can I achieve this ?

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure about masking or making not reply-able, but I use this to set the address that will show as the sender:

    .SentOnBehalfOfName = "Whatever@Blah.com"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    ragsgold is offline Access & Excel Developer
    Windows XP Access 2000
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    Thanks a lot this worked and I was able to put the from sender name as desired

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Mail Merge
    By Nixx1401 in forum Access
    Replies: 1
    Last Post: 02-15-2010, 10:51 AM
  2. Mail Merge document locked when opened from Access
    By retro in forum Import/Export Data
    Replies: 0
    Last Post: 08-14-2009, 03:12 AM
  3. Mail Merge from Access to Word
    By Rachelkm2 in forum Programming
    Replies: 1
    Last Post: 05-29-2009, 02:49 PM
  4. Mail merge
    By grgerhard in forum Forms
    Replies: 0
    Last Post: 04-25-2006, 05:06 PM
  5. Mail Merge problem
    By kfergus in forum Programming
    Replies: 0
    Last Post: 04-24-2006, 01:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums