Perfecting email body

[Banderas23]

I have some code that creates an outlook email and populates the message to field and aubject field as I need.

When it comes to the body I cannot get it to format how I need. I've tried various command I have read online and it just doesn't seem to work.

Can anyone help fill out the missing code please:

The form has numerous fields and at the end of entering a form I want to click send and it email a copy of some of the fields. I'm struggling with the formatting.


Private Sub Command219_Click()

Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
' prevent 429 error, if outlook not open
On Error Resume Next
Err.Clear
Set oOutlook = GetObject(, "Outlook.application")
If Err.number <> 0 Then
Set oOutlook = New Outlook.Application
End If

Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
If IsNull(Me.Email) Then
MsgBox "This customer has no email address."
Else
.To = Me.Email
.Subject = "Email Confirmation: " & Me.Reference_nu
.Body = "Dear " & Me.CustomerName
.Display
End If
End With
Set oEmailItem = Nothing
Set oOutlook = nothign
End Sub


I want the .body to read like this:-

Dear customer name

This is your email confirmation for order number reference_nu on date Date costing a total of Price

Reference, date and price being fields in the form.

I'm struggling getting the carriages between dear customer and the next line of text!

Any help would be much appreciated

Using access2013

Thanks

Antony

 

[Cronk]

.Body ="Dear " & Me!CustomerName & vbcrlf & "This is your email confirmation for order number " & me.referenceNo & "on " & format(Me.order, "your preferred dateformat") & " costing a total of " & Me!Price

 

[Banderas23]

Does the vbcrlf add a carriage return. As I'm sure untried this and it failed. I then read up on other commands such as chr(13) which also failed..

Let me try this again il see how I get on.

Thanks

 

[Banderas23]

It keeps failing when it tries to add form fields in the main body text.

One of my fields is customername but as soon as it looks up me.customername it fails.

I know this field exists as when I type in the code it's in the list me.customername

 

[GinaWhipp]

Is Customer Name the name of the field or the name of the Control?

 

[Banderas23]

Name of the field. I've even changed the name of the field to customer_name.

 

[GinaWhipp]

You need to use the name of the Control on the Form.

 

[Banderas23]

If I use .Body ="Dear " & Me.CustomerName

This brings up the customer name.

When I add the following to get a carriage return and continue formatting my message body the code above fails. It's like I'm missing something code wise.

As soon as I add this


& vbcrlf & "This is your email confirmation for order number " & me.referenceNo & "on " & format(Me.order, "your preferred dateformat") & " costing a total of " & Me.Price

Thanks for your help

 

[Grumm]

Try to use

.HTMLBody = "Dear " & Me.CustomerName & "<br>...."

So for each enter use <br>
That should work fine.

 

[Banderas23]

As much as I hoped this would work, I got a compile syntax error.

 

[Grumm]

Can you show what you try to do ?

 

[Banderas23]

 

[Banderas23]

Everything up to .body="dear " & me.customername works fine. I just want to continue with text on another line and the code just fails.

 

[Banderas23]

What I have done now is get rid of some of the other form controls and I get the added text but it does not add in a carriage return.

 

[Minty]

Can I suggest you build up the body string separately as strBody in your code then simply use
.body = strBody

This will allow you to debug.print strBody and see exactly what you are having issues with?

 

[Grumm]

Personally i never use Me.ControlName.
Don't know if it makes a huge difference but i always do Me.ControlName.value.

If you still have problems, break that string down to different lines

Dim sBody as String
sBody = "dear " & me.customername
sBody = sBody & "<BR> This is your email confirmation for order number " & me.referenceNo
sBody = sBody & "....

 

[Banderas23]

Sounds good, how do I do that?

I actually got some code that was in other databases and then I use it in my own and work out what needs changing in order to get mine to do what I need.

How do I build up strBody and what do I put in the code to do this?

 

[Banderas23]

Minty... Worked it out. Built up the strBody and then I built up my email with form controls and it's working fine with no errors..

Cheers for everyone's input I've managed to get another section of my database completed.

Thanks again everyone.