I need a Macro that will generate multiple reports and e-mail each report to a specific person.

[craig1988]

Hi

I hope somebody can help me with this.

I have a DB that is used to track errors in INPUTTERS work. Each error is given a timestamp. There are 40+ inputters who are identified by their Staff ID.

Every week, I would like to run a macro that generates a report for each staff ID and automatically e-mails the report to the e-mail address of that staff member.

Please let me know what additional information you may need to help me.

[ranman256]

I have a table that holds my reports list tReports: [rptName],[eMailQry]
I use a form that has my reports list (listbox), once I select the report, it also shows the list of emails for that report. The query in [eMailQry].
The code below then scans each email and sends that report to them. (tho my 'reports' are queries, you can set yours to send snapshots, or pdfs)

 
'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer
For i = 0 To lstEAddrs.ListCount - 1
   vRpt = lstEAddrs.ItemData(i)
   lstEAddrs = vRpt
   vTo = lstEAddrs.Column(2)
   
   vBody = "body of email"
   vSubj = vRpt
   vFilePath = ""  'path of the snapshot or XL or PdF
   
   Call Email1(vTo, vSubj, vBody, vFilePath)
Next
End Sub
 
'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark OUTLOOK in the vbE menu, Tools, References
'-------
Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody,optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody
    If Not Ismissing(pvFile) Then  .Attachments.Add pvFile, olByValue, 1
    
.Send
End With
EmailO = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function
ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function

[craig1988]

Thanks for the reply ranman256

Do you recomend this as being the best way to go about this? I did not realise you could have reports saved in a table. Are they hyperlinks to the saved reports or are they just another field within the table? I should have mentioned that I do not work with access that often although I can get my head around the VBA.

The number of Reports will change from time to time because of new staff beginning/leaving so will this be an issue? I also do not want to store the reports once they are e-mailed to the staff so can they be deleted within the same macro?

Thanks again for the help. Hopefully I can get working on this soon.

[June7]

I don't think ranman is save a report to table, is just saving a text value. I am not following how the code sends query data. Also not sure what is meant by 'scans each email'.

[craig1988]

June7

Could you suggest a different approach to take to make my original ask possible??

Thanks

[June7]

Sending emails is common topic. Procedure posted by ranman has basic code common to many examples. The approach taken depends on what you want.

1. send one email to multiple recipients - 40 would be a long To line

2. send individual email to each recipient - sounds like this is what you want

Both options involve opening a recordset in VBA and looping through the records.

Simplest code would use SendObject method. But if you want attachments need to use the approach shown in ranman's example.

Do you want all info in the email body or do you want to send Access report as email attachment?

Review https://www.accessforums.net/program...ook-21903.html

[craig1988]

June7

You seem to have a firm grasp of what I am after.

I had a look at the below code and I believe this is what I am looking for. I Would like to send an individual e-mail to each recipient.

'open Outlook, attach zip folder or file, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address"
''.cc = ""
''.bcc = ""
.Subject = "text here"
.HTMLBody = "text here"
.Attachments.add ("path\filename")
''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.send
End With



I would like the body of the e-mail to contain the report as opposed to an attachment.


Can you help me get started with this or have you any advice on how I should set up my tables/queries/reports

[craig1988]

June7

I have made some progress with a tutorial I used. I can now send e-mails to the e-mail list ( I have set up a test environment with 4 e-mail addresses).

Now I have to begin to build the body of the e-mail.

Ideally I would like it to read roughly like this;

Hi "FirstName",

For the week of "Date based on the query parameter" you made "Number of errors within the date range" errors. To review any of these, please see the table below.

<< The report would go here >>

[June7]

Code needs to open a recordset and loop through the records to pull data into the message construct. Also probably need a recordset of the email addresses and loop through that. This could mean two recordsets and two looping structures (nested).

Building a 'table' within the body would probably involve HTML code.

Embedding report as an image would require saving report as a jpg, png, bmp file (not PDF). Access doesn't export to those file types http://www.mrexcel.com/forum/excel-q...rably-jpg.html. Then HTML img code tag can pull in the file.

Access will export report to XML and HTML format. Maybe one of those files can be pulled into email body.

[craig1988]

June7

I'm really starting to get my head around all of this now so thnks again for the help.

I now understand recordsets, and loops. Could you give me some help with a nested structure. I have some knowledge of javascript so the concepts are not completely alien to me.

I think what I need to do is to run (the same for each staff ID) a query and export that query in a readable format (that is why I thought a table/report would be appropriate) into an e-mail and send it to the e-mail address that is connected to that Staff ID.

I am very comfortable with html so I could investigate using that. If I was to pull an <img> would it not require me to save the file somewhere first?

More importantly, if you could give me some advice on the loop within a loop (nested).

[June7]

Roughly, something like:

Sub SendMail()
Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
Dim ...
Set rsEmails = CurrentDb.OpenRecordset("SELECT Address FROM Staff;")
While Not rsEmails.EOF
    Set rsData = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE StaffID=" & rsEmails!StaffID & ";")
    While Not rsData.EOF
        'code to build email body using rsData record
        strBody = strBody & rsData!field1 & ", " & rsData!Field2 & ", " & rsData!field3 & vbCrLf
        rsData.MoveNext
    Wend
    'code to send email
    ...
    rsData.Close
    rsEmails.MoveNext
Wend
End Sub

Yes, as stated, image would have to be saved file.

[craig1988]

Hi June7

I am really appreciating all your help. I have experimented with your code but I could not get any results I wanted. I would really appreciate it if you could explain in a bit more detail the code in your last comment.

[June7]

What exactly do you need explained?

The code opens two recordsets.

One recordset has all the email addresses.

Other recordset has the data to construct email body. It is filtered to the StaffID of the current record of the first recordset. Ooops, I forgot to include StaffID field in the first recordset.

The code 'loops' - reads each record and uses the data.

Adapt as appropriate for your data. Post attempted code for analysis.

[craig1988]

Hi June7

I understand the proccess but I am not sure what what each line of the code relates to in my tables.

<code>
Public Function SendTestEMail()
Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
Set rsEmails = CurrentDb.OpenRecordset("SELECT [T: StaffList].[e-mail], [T: StaffList].Forename FROM [T: StaffList];")
While Not rsEmails.EOF
Set rsData = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE StaffID=" & rsEmails!StaffID & ";")
While Not rsData.EOF
'code to build email body using rsData record
strBody = strBody & rsData!field1 & ", " & rsData!Field2 & ", " & rsData!field3 & vbCrLf
rsData.MoveNext
Wend
'code to send email
rsData.Close
rsEmails.MoveNext
Wend
End Function
</code>

Here is what I hav so far. I know there are mistakes can you point me in the right direction

[June7]

Use your table and field names instead of the dummies I used. Do you have table named Data, field named StaffID? If not, use what is appropriate.


BTW, advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

CODE tags use [] not <>.