Email Report - auto fill subject line (1 Viewer)

Gismo

Registered User.
Local time
Tomorrow, 01:33
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I want to email a report using emaildatabaseobject in a macro but would like to auto fill the subject line with data from the report
I am using Outlook

i have below code, which i found on this site but not sure where to add this code
I still need to edit the code to my needs
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM WhateverTable"

Set rst = Currentdb.OpenRecordset(strSQL)

Do Until rst.EOF
WhateverYourEmailCodeIs.Subject = Format(rst!FieldNameHere, "0000") & rst!TheOtherFieldNameHere
WhateverYourEmailCodeIs.Send
rst.MoveNext
Loop

rst.Close

Set rst = Nothing
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:33
Joined
Feb 28, 2001
Messages
27,001
That code won't work until the mail object exists.

Look in this thread to see one example that sends a single message.


Note that you only create one Outlook application object so that has to be outside your loop. But you create more than one mail object so that can be inside your loop.

And because Outlook has a life of its own when created this way, you might need to tell it to go away on the other side of the loop, when cleaning up things. If you don't, Access can exit and leave Outlook still open.

Here is a more detailed link that gives you a place to browse. (Look at the navigation index to the left of that page when in your browser).

 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:33
Joined
Oct 29, 2018
Messages
21,358
Hi Gismo. You said the data for the email is coming from a report. Is it possible to look up that data from a table? If so, you may be able to use DLookup(). Just a thought...
 

vba_php

Forum Troll
Local time
Today, 18:33
Joined
Oct 6, 2019
Messages
2,884
gismo,

based on what DOC says, the outlook instance has many object associated with it. one, which you'll see in those links he gave, is the email object. is has many properties. one is called .SUBJECT or .TITLE. can't remember which, but intellsense will let you know.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:33
Joined
Feb 28, 2001
Messages
27,001
OK, a clarification appears to be in order since Adam muddied the waters a bit with vague advice.

You create an Outlook Application Object in either of two ways: By using GetObject or CreateObject. The preferred method is to attempt to use the GetObject first because Outlook gets kind of "snippy" with you if you try to create a new application object when one already exists. Unlike Access or Excel, which can have two or more instances of itself running at the same time on the same machine under the same user, Outlook must be exclusive for that machine and user.


So now you have the Outlook app object. It is, like Access, a holder of many objects. For Access, you have tables, queries, forms, reports, etc. For Outlook you have mail objects, task objects, calendar objects, address book objects, contact objects...

If you want to send mail, create a mail object. The earlier link in post #2 will show that. You create the object, populate it with various properties such as .To, .CC, .BCC, .Subject, and .Body to name a few. You can also add Attachment objects to the Mail object. At some point you are finished, at which time if you don't want to actually look at it, you can just .Send it.

Once you send the mail object, it is no longer where you were working on it; therefore, you can't add attachments after sending a mail object and expect to see the attachments at the other end. The mail message will be moved to your Outlook's .Outbox folder until it gets sent, at which time it will move to your .Sent Items folder. Since this is "real" Outlook, if you had any Rules defined that related to outgoing mail, they would apply.
 

vba_php

Forum Troll
Local time
Today, 18:33
Joined
Oct 6, 2019
Messages
2,884
OK, a clarification appears to be in order since Adam muddied the waters a bit with vague advice.
oh come on richard, it wasn't THAT bad. =) but thanks for the details. I'm sure this person appreciated it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:33
Joined
Feb 28, 2001
Messages
27,001
Thank you, zeroaccess, because that would be simpler. I was answering the question implied by the original post that was using an Outlook-app style. But you are quite correct that SendObject allows the information to be included in that single line.
 

Gismo

Registered User.
Local time
Tomorrow, 01:33
Joined
Jun 12, 2017
Messages
1,298
Hi All,

Thank you for the comments
sorry for the late reply
still not sore how to email a report with data from the report to be automatically populated in the subject line
am i missing something from your replies?
 

bastanu

AWF VIP
Local time
Today, 16:33
Joined
Apr 13, 2010
Messages
1,401
Hi Gismo,
You did not explain what exactly your report emailing need is. Do you have a report to be sent to one (or multiple such as a distribution list) recipient such a daily summary report or a class list. In this case you could simply use a dLookup to get the subject from the query/table that is the report record source (because would be unique).
Now if you want to email individual reports to individual recipients and you want to customize the subject line for each one (such as emailing receipts or invoices and you want to say "LName, FName, here is your invoice for March 2020") then it gets more complicated. I suspect this is what you want as your original code is showing how to loop in a recordset.

The basic setup I use for this is to use a loop in a recordset based on a query (the report's recordsource the way you have it now that returns all recipients), get the unique ID for each record and set a global variable to that value. The report recordsource needs to get changed to a new query that returns only one record and to do that I simply use a public function in a standard module that returns the global variable set in the loop.

Code:
Option Explicit

Public lngUniqueID as long

Public Function vcUniqueIDForEmail() as long
vcUniqueIDForEmail=lngUniqueID
End function
Using your example:
Code:
Dim rst As DAO.Recordset
Dim strSQL As String
Dim sSubject as string
Dim sTo as string
strSQL = "SELECT * FROM WhateverTable"

Set rst = Currentdb.OpenRecordset(strSQL)

Do Until rst.EOF
lngUniqueID=rst("UniqueID")
sSubject.Subject = Format(rst!FieldNameHere, "0000") & rst!TheOtherFieldNameHere
sTo=rst("EmailAddress")
DoCmd.SendObject acSendReport “rptYourReport”, acFormatPDF, sTo, , , sSubject, "YOUR MESSAGE", True
rst.MoveNext
Loop

rst.Close

Set rst = Nothing

Not knowing what option you need I will stop here for now.

Cheers,
Vlad
 

Gismo

Registered User.
Local time
Tomorrow, 01:33
Joined
Jun 12, 2017
Messages
1,298
Hi Gismo,
You did not explain what exactly your report emailing need is. Do you have a report to be sent to one (or multiple such as a distribution list) recipient such a daily summary report or a class list. In this case you could simply use a dLookup to get the subject from the query/table that is the report record source (because would be unique).
Now if you want to email individual reports to individual recipients and you want to customize the subject line for each one (such as emailing receipts or invoices and you want to say "LName, FName, here is your invoice for March 2020") then it gets more complicated. I suspect this is what you want as your original code is showing how to loop in a recordset.

The basic setup I use for this is to use a loop in a recordset based on a query (the report's recordsource the way you have it now that returns all recipients), get the unique ID for each record and set a global variable to that value. The report recordsource needs to get changed to a new query that returns only one record and to do that I simply use a public function in a standard module that returns the global variable set in the loop.

Code:
Option Explicit

Public lngUniqueID as long

Public Function vcUniqueIDForEmail() as long
vcUniqueIDForEmail=lngUniqueID
End function
Using your example:
Code:
Dim rst As DAO.Recordset
Dim strSQL As String
Dim sSubject as string
Dim sTo as string
strSQL = "SELECT * FROM WhateverTable"

Set rst = Currentdb.OpenRecordset(strSQL)

Do Until rst.EOF
lngUniqueID=rst("UniqueID")
sSubject.Subject = Format(rst!FieldNameHere, "0000") & rst!TheOtherFieldNameHere
sTo=rst("EmailAddress")
DoCmd.SendObject acSendReport “rptYourReport”, acFormatPDF, sTo, , , sSubject, "YOUR MESSAGE", True
rst.MoveNext
Loop

rst.Close

Set rst = Nothing

Not knowing what option you need I will stop here for now.

Cheers,
Vlad
thank you, i will work through this
 

Cronk

Registered User.
Local time
Tomorrow, 10:33
Joined
Jul 4, 2013
Messages
2,770
Re post 10, the code will generate the report for every recipient. If there are many recipients and/or the report is complex, the processing time could be significant. In which case, the report should be generated outside the loop and the emails generated in the loop, attaching the saved report.
 

Gismo

Registered User.
Local time
Tomorrow, 01:33
Joined
Jun 12, 2017
Messages
1,298
Hi all,

i just can net get the code in post 10 to work
tried a few options

i would like to use outlook to attach a report, in the subject line i would like to select a few fields from the report
subject like to be unique from selected fields in the report being send
 

bastanu

AWF VIP
Local time
Today, 16:33
Joined
Apr 13, 2010
Messages
1,401
Do you need one report per record attached to a customized email? Can you please post what you tried?

Cheers,
Vlad
 

Gismo

Registered User.
Local time
Tomorrow, 01:33
Joined
Jun 12, 2017
Messages
1,298
Thank you all, did a few emails with different reports adding multiple fields from the report in the subject line of the mail
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:33
Joined
Oct 29, 2018
Messages
21,358
Thank you all, did a few emails with different reports adding multiple fields from the report in the subject line of the mail
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom