How to log email from Access 2007

[Aximboy]

 
I have a button on my form that sends & attaches a report to an email.
How do I create a log (with automatic entry) of when an email was sent, and if possible to whom it was sent.

Please help, thanks.

 

[genomon]

Create a table containing the data fields you need.
Capture the email information in a variable.
Insert a record into the table, probably with DoCmd.RunSQL.

Cogito eggo sum – I think, therefore I am a waffle.

 

[Aximboy]

Thanks genomon for your very quick reply.
Pardon my ignorance, but how do I capture the email info in a variable? Can you give me a sample code please?

 

[genomon]

I have a button on my form that sends & attaches a report to an email.

Post back the code behind this button, and I will help you modify it to do what you need.

Cogito eggo sum – I think, therefore I am a waffle.

 

[Aximboy]

I do not have a code behind the command button, but I do have a macro on the "On Click" property.

On the macro, I do have an "OpenReport" action which opens the report I want to attach, then a "SendObject" action which attaches a snapshot file format of the attachment, and finally the "Close" action to close the open report after sending the email.

Thanks again in advance.

 

[genomon]

Sorry, don't know how you would capture the data with a macro. You can convert the macro to VBA and then we can create some variables and a log table. Then you replace the macro call with the VBA code in the button's click event.

Cogito eggo sum – I think, therefore I am a waffle.

 

[genomon]

If you are unfamiliar on how to accomplish the conversion, here is a link with a brief overview of programming, and how to do the conversion:

http://office.microsoft.com/en-us/access/HA012142131033.aspx

Cogito eggo sum – I think, therefore I am a waffle.

 

[Aximboy]

Thanks genomon.

I've finally converted my macro into VBA. Here it is...

Private Sub Command46_Click()
On Error GoTo Err_Command46_Click
 
    Dim stDocName As String
    Dim stMailFormat As String
 
    stDocName = "rptGradedAggregateBase2-Test"
    stMailFormat = "Snapshot Format"
    DoCmd.SendObject acReport, stDocName, stMailFormat
 
Exit_Command46_Click:
    Exit Sub
 
Err_Command46_Click:
    MsgBox Err.Description
    Resume Exit_Command46_Click
   
End Sub


Again, thanks in advance.

 

[genomon]

I'm not seeing any "to" field data in the SendObject method.
This would be the person(s) the mail is being sent to. Here is the syntax of SendObject, followed by a code sample:

DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
    "[b]Nancy Davolio; Andrew Fuller[/b]", "Joan Weber", , _
    "Current Spreadsheet of Employees", , False

If you can find the code that has the send-to data, that is what needs to be captured.
Assuming you can find it, create a table with aminimum of fields for sent-to and datetime (let's call them SendTo, SendTime), and I would strongly recommend a key field (although you could use these two to create a clustered primary key). Then, you simply capture & insert that data (let's assume it's stored in a variable called strSendTo, and your table is called tblEmail)& the current datetime:

Dim datDatetime As Date
Dim strSQL As String

datDatetime = Now

strSQL = "INSERT INTO tblEmail(SendTo, SendTime) "
strSQL = strSQL & "VALUES('" & strSendTo & "', "
strSQL = strSQL & "#" & datDateTime & "#)"

DoCmd.RunSQL strSQL

You will want this code to run for each iteration of the send mail code.

Cogito eggo sum – I think, therefore I am a waffle.