Question Recording an email has been sent (time & date, subject and body)

[daveblanch]

Hi all,

Apologies if this is in the wrong forum, wasn't confident which it would fall under so plumped for general.

My problem I don't know how to record emails that are being sent from Access.

My database sends emails using Outlook with no problem. The form has two text boxes, txtSubject (.subject) and txtBody (.body) which the user completes before hitting send. The code ensures that an email is only sent to those records that have checkbox chkMailMe checked.

What i need to do is record, for each record that has an email sent the date and time, txtSubject and txtBody in a table that has a one to many relationship with my 'Main' table in order to maintain an audit trail.

Can anyone please help?

Thanks,
David.

 

[DavidAtWork]

Just create an email log table (tblEmailLog) with the desired fields, go to your code and after the email has been sent add:
Docmd.SetWarnings False
Docmd.RunSQL("INSERT INTO tblEmailLog(Main_ID, mySubject, myTextBody, myDateTime) VALUES('" + str(Me.MainID) + "', """ & Me.txtSubject & """, """ & Me.txtBody & """, '" + format(Now()) + "')")
Docmd.SetWarnings True

Me.MainID will be your main form's record ID that you said has a one to many relationship with my 'Main' table, and assumed to be a number. Using the Me. reference assumes the email code is running as a private sub within your main form, but if the email code is a function called, then you may need to replace the Me.txtSubject with the full reference such as Forms!frmMain.txtSubject for example