Email from access with form's field details

[Stephd8459]

Hi All,
I've done some searching and haven't been able to resolve this one, hoping someon ecan help or point me to a site.
I have a Module that all my email automation code is included in I can send an email and all works great.
What I'd like to do is include some details from the current form the user is on when they select to send an email

Code is below-

[COLOR=black][FONT=Verdana]'this is the call event that happens when the button click on the form [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'to send justification email launches frmContacts[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub cmdSendJustification_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Call SendEmail(Me.sEmail)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End sub[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]'this is the code in the modules[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Sub SendEmail(varTo As Variant)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Dim mailItem As Outlook.mailItem[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Dim varCont As Variant[/FONT][/COLOR]
 
 
[COLOR=black][FONT=Verdana]   InitOutlook[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Set mailItem = outlookApp.CreateItem(olMailItem)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.To = varTo & ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Subject = "Contract Renewal Justification"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "As you may know we are now required to get justification on maintenance contracts in an effort to identify exactly what we have under maintenance and to try to reduce costs where possible." & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Contract:"  & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Vendor:" & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Effective Date:" & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Expiration Date:" & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Annual Spend:" & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Description:" & Chr(13) & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Can you fill out the attached questionnaire for each contract and return it to me? " & Chr(13) & Chr(13)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Body = mailItem.Body & "Thanks,"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Display[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   'to send attachement[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   mailItem.Attachments.Add "[URL="http://integratedtechnology.monsterworldwide.com/Planning%20and%20Implementation/Budget%20Information/Document%20Library/2010%20Maintenance%20Budget/2010%20Maintenance%20Renewal%20Justification%20Form_rev4.dotx"][COLOR=#22229c]Weblink[/COLOR][/URL]", olByValue, 1, "Renewal Justifcation Form"[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]   Set mailItem = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   CleanUp[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

What I'm looking to do is get the "Contract" data from the form inserted into the email
Have tried something like this

[COLOR=black][FONT=Verdana]mailItem.Body = mailItem.Body & "Contract:" & [Form]![Support Detail]![Contract] & Chr(13)[/FONT][/COLOR]

I get a compile error: External Name not defined

If anyone has a format that will get me the data from the current open form I'd really appreciate the help.

Thanks

 

[boblarson]

Build your string for the body completely before assigning it and make sure your form name is spelled correctly in your code (Suportt) - if that is how you really have the form spelled the code should work but usually it is spelled Support so you might have spelled your form wrong in the code.

Build the string first (and Chr(13) needs to be used with Chr(10) - or just use vbCrLf:

Dim strBody As String
 
strBody = "As you may know we are now required to get justification on maintenance contracts in an effort to identify exactly what we have under maintenance and to try to reduce costs where possible." & vbCrlf & _
[COLOR=black][FONT=Verdana]"Contract:"  & [B][COLOR=red]Forms[/COLOR][/B]![Suportt Detail].Contract & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Vendor:" & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Effective Date:" & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Expiration Date:" & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Annual Spend:" & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Description:" & vbCrLf & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Can you fill out the attached questionnaire for each contract and return it to me? "  & vbCrLf & vbCrLf & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]"Thanks,"[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]mailItem.Body = strBody[/FONT][/COLOR]

Also the form reference is FORMS!YourFormName, not [Form]

 

[Stephd8459]

Bob, Thanks.
I actually had caught and corrected the typo... had some issues posting the thread must have missed updating...
Thank you the resolition worked perfect
On the Chr(13) needs to be with Chr(10), why? or can you tell me some place to get a better understanding? I'm not getting any errors using it alone.

Steph

 

[boblarson]

Bob, Thanks.
I actually had caught and corrected the typo... had some issues posting the thread must have missed updating...
Thank you the resolition worked perfect
On the Chr(13) needs to be with Chr(10), why? or can you tell me some place to get a better understanding? I'm not getting any errors using it alone.

Steph

When using a Carriage Return you need to include a Line Feed as well. It is a legacy thing from old line printers but the code really should have them both to work together.

You can use

Chr(13) & Chr(10)

like that

or you can use

vbCrLf

or

vbNewLine

but if you try to use

Chr(10) & Chr(13) in that order, it will fail because you can't have a line feed before the carriage return.