Sending Email based on user name in form/Table

[pcride]

I have searched and searched and have not found the answer, I am getting close so I got stuck and I am posting.

Using Access 2007
I have a button that opens a Report based on a unique ID that I search for. The Report Opens and does contain a field for the email address. I have added an email button and can get the form to email as an attachment, but I want to have the email address populated based on the Report field in the TO field in Outlook

How can this be done? I have tried many codes.


Thanks in advance.

 

[fibayne]

Hi ...been working on this myself and through threads on this forum I have this working from a command button on my form...hope it helps..Fi



Private Sub cmdBUTTON_Click()
Dim sSubject As String
Dim stMail As String
Dim sDocName As String

sDocName = "REPORTNAME"
'message subject
sSubject = "MESSAGE TO RECIPIENT dated " & Date & Me.cboClient
stMail = DLookup("", "TABLESTORINGMAILADDRESS", "[ID]= Forms![NAMEOFFROMBUTTONISON]![IDFIELDONFROM]")

DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acSendReport, sDocName, acFormatPDF, _
stMail, , , sSubject, "EMAILSUBJECTMESSAGE ", True

End Sub

 

[pcride]

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:
Method or data member not found
---------------------------
OK Help
---------------------------


I get that error message at .cboClient

Any Ideas?

 

[fibayne]

Hi...apologies cboClient was the name of the field on my form basically I wanted to show a message todays date and the name of the client in the subject line of the email, all you need is

sSubject = "YOURMESSAGETORECIPIENT"

for this line

'Method or data member not found' basically means the name of the fileld you are quoting in this case cboClinet does not exist on your form

Hope this gets it for you....Fi

 

[pcride]

So what would I put there? Can I just add the subject without the rest of the data ?

sSubject = "MESSAGE TO RECIPIENT"

??

 

[fibayne]

Hi...you need to use all the code

sSubject = "MESSAGE TO RECIPIENT"

"Message to Recipient" is the opersonalised message you want to show in the subject line of the email

Fi

 

[pcride]

This is my information

My table is called TableMain
The field in the table containing the email address is called EmailAddress
the form that I have has the email field called EmailAddressFrm
The report field that contains the email is called EmailaddressRpt

I don't understand what I put in place of the following from your example
field_of_where_condition
& parameter_here
'---- if where condition was numeric <-- is this just a comment?

Still pretty fresh at these forms.

Thanks for your help.

 

[fibayne]

Replace the following

cmdBUTTON - Name of the button you click on the form
REPORTNAME – Name of the report you are attaching to the email
MESSAGETORECIPIENT – whatever you want the message to be it the subject line of the email
IDFIELDOFTableMain – The name of the ID field on TableMain
NAMEOFFORMBUTTONISON – The name of the form that the button you click is on
IDFIELDONFORM – The name of the ID field on the form that your button is on, if your forms control source is TableMain then you more than likely have the same ID field name as is on your table
EMAILSUBJECTMESSAGE – the message you would like to show on the body of the email

field_of_where_condition
& parameter_here
'---- if where condition was numeric <-- is this just a comment?

Not sure where you are getting this as it isn’t on the code posted ?

Fi

 

[pcride]

Ok, finally... everything works just fine. I had the wrong field name in the wrong spot

sDocName = "Report Name"
'message subject
sSubject = "Email Subject"
stMail = DLookup("[name of field with email address in table]", "TableName", "[name of field with email address in table]= Forms![Form Name]![Field Name on Form]")

DoCmd.SendObject acSendReport, sDocName, acFormatXPS, _
stMail, , , sSubject, "Email Message Here", True
End Sub




Also, I noticed the form has to be opened other wise the code fails. Cant I add to the code to open the form and if its already open then do nothing?

 

[pbaldy]

Search on IsLoaded for methods of doing that.

 

[pcride]

How can I hard code a few email addresses to the following code?

sDocName = "Report Name"
'message subject
sSubject = "Email Subject"
stMail = DLookup("[name of field with email address in table]", "TableName", "[name of field with email address in table]= Forms![Form Name]![Field Name on Form]")

DoCmd.SendObject acSendReport, sDocName, acFormatXPS, _
stMail, , , sSubject, "Email Message Here", True
End Sub



Thanks,

 

[pcride]

I found this.

DoCmd.SendObject acSendReport, "MyReportName", acFormatPDF, [To], [Cc], [Bc], [Subject],[MessageText],[EditMessage],[TemplateFile]


How do I use the [CC]

[CC]myemail@domain.com, ??

 

[pbaldy]

..., "myemail@domain.com", ...

 

[pcride]

Thanks I have tried that but I keep getting an error.

What is the underscore after the format type i.e acFormatXPS, _


So it would be
DoCmd.SendObject acSendReport, sDocName, acFormatXPS, _ , email@email.com,

 

[pbaldy]

The space-underscore are line continuation characters. As you have it, take them out. Also, the quotes around the email address I had are necessary.

 

[pcride]

So does access automatically define the TO CC BCC in that order when using the docmd.

 

[pbaldy]

If you look in VBA help, the positions are specified there.