Email separate reports in PDF Format using outlook (1 Viewer)

Access_Help · Jun 12, 2018

I am sure that there are numerous threads on this already, but I can't seem to find the one that fits my scenario:

I have a relational database with a table with the names of recipients and their email addresses and I have a report based on a query.

I want to e-mail the report in PDF format for each recipient in a separate email with the click of a single button.

So for example, if I select the name of the recipient from a combobox, it will convert the report for that recipient into PDF and also email it to them - or it can send it to all the recipients in the combobox all in one go.

As I am not a programmer, I need a straight forward script which can easily be amended.

Many thanks in advance!

Ranman256 · Jun 12, 2018

in the form, put a listbox of the persons: ID, email.
the query of the report uses the listbox to pull only that persons data, (bound to ID)
the code get the ID in the list (col 1) and grabs the email (col 2)

Code:

'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer

vRpt = "rReport1"
vQry = "qsData"
vBody = "body of email"
vSubj = vRpt

     'scan the list box
For i = 0 To lstEAddrs.ListCount - 1
   vItm = lstEAddrs.dataitem(i)   'get new item
   lstEAddrs = vItm                   'set list box to item
   vTo = lstEAddrs.Column(1)      'col 1 has ID, col2 has email  (in vb, combo box items starts with 0)
     
      'send report
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody

Next
End Sub

Uncle Gizmo · Jun 12, 2018

Access_Help said:
I am sure that there are numerous threads on this already, but I can't seem to find the one that fits my scenario:

It is unlikely that you will find exactly what you want. However with a bit of effort and advice from other forum members you should be able to cobble something together.

See my blog here where I describe how you can send multiple reports in pdf format...

http://www.niftyaccess.com/generate-multiple-reports/

In particular note the link to Gina Whipps website:-

http://www.access-diva.com/vba16.html

where she shows you how to set up the email part.

Sent from my SM-G925F using Tapatalk

Access_Help · Jun 12, 2018

I have started playing with Ranman's script - thank you Ranman
I created a list and named it lstEAddrs and amended the code to suit the database, upon running the code , I am presented with this error:

Access_Help · Jun 12, 2018

Uncle Gizmo,

Gina's script works perfectly for sending the same document to multiple recipients.

How can I add my e-mail signature to the mail and format the writing with line breaks?

Gasman · Jun 12, 2018

I tend to use the signature for the account sending the email?

As for line breaks, if it is html, you have to play around with it.

Here is something I posted earlier when I was having problems using SendUsingAccount.

I had to insert my body of the email in front of the signature, the strDiv is where the body starts. There will likely be better ways but I am learning as I go along.

If it is just a non HTML email I believe you can just use vbCrLf

Code:

    strDiv = "<div class=WordSection1>"
    strBody = "<p>" & strGreeting & "</p>" & pstrMessage
    ' Now comes from email table
    'strBody = strBody & "<p>" & strTimeLimit & "</p><p>"
    strBody = strBody & "<p>Should there be any queries please let me know.</p>"

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Need to get correct account to send on
    For Each OutAccount In OutApp.Session.Accounts
        If OutAccount.DisplayName = pstrCompany Then
            Exit For
        End If
    Next

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .SendUsingAccount = OutAccount
         'Debug.Print OutAccount.DisplayName & " & " & OutAccount.smtpAddress
         
        .Display
        .To = pStrToNames
      .BCC = ""
        .Subject = pstrSubject
        .htmlbody = Replace(.htmlbody, strDiv, strDiv & strBody)
        '.attachments.Add pstrFilename & ".pdf
        .Attachments.Add pstrFilename

        .Send
    End With

HTH

Access_Help said:
Uncle Gizmo,

Gina's script works perfectly for sending the same document to multiple recipients.

How can I add my e-mail signature to the mail and format the writing with line breaks?

isladogs · Jun 12, 2018

Access_Help said:
Uncle Gizmo,

Gina's script works perfectly for sending the same document to multiple recipients.

How can I add my e-mail signature to the mail and format the writing with line breaks?

I've just answered a similar question in post 17 of this thread
http://www.accessforums.net/showthread.php?t=72417

Ranman256 · Jul 5, 2018

The listbox has 2 or 3 columns, from the query
1 for email addr
1 for persons name
1 for key (if needed)

pull the item the user chose,from the columns you included in the query
NOTE: IN VB COLUMNS BEGIN WITH ZERO
so column 1 on the form is column(0) in code.

Code:

For i = 0 To lstEAddrs.ListCount - 1
  vItm = lstEAddrs.itemData(i)   'get new item
  lstEAddrs = vItm                     'set the listbox to this item

  vName = lstEAddrs.Column(2)
  vTo = lstEAddrs.Column(1)
  VBody = "Dear," & vName & vbcrlf & "Do you like me?"

   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
next

Access_Help · Jul 10, 2018

Ranman256 said:
The listbox has 2 or 3 columns, from the query
1 for email addr
1 for persons name
1 for key (if needed)

pull the item the user chose,from the columns you included in the query
NOTE: IN VB COLUMNS BEGIN WITH ZERO
so column 1 on the form is column(0) in code.

Code:

For i = 0 To lstEAddrs.ListCount - 1 vItm = lstEAddrs.itemData(i) 'get new item lstEAddrs = vItm 'set the listbox to this item vName = lstEAddrs.Column(2) vTo = lstEAddrs.Column(1) VBody = "Dear," & vName & vbcrlf & "Do you like me?" DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody next

That works brilliantly, thank you!

I need to filter the query to display data in the report separately for each of the recipients in the listbox, how do I achieve this?

Email separate reports in PDF Format using outlook (1 Viewer)

Access_Help

Registered User.

Ranman256

Well-known member

Uncle Gizmo

Nifty Access Guy

Access_Help

Registered User.

Attachments

Access_Help

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Ranman256

Well-known member

Access_Help

Registered User.

Similar threads

Users who are viewing this thread