Email from a form

[mousemat]

Hi all

I have a form which has a button to email the data out in a standard email message.

Here is the code:

Private Sub Command60_Click()
Dim MyDb As dao.Database
Dim rsEmail As dao.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("SELECT * FROM qryDelegateNames WHERE CourseID = " & CourseID & "")
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(9)) = False Then
sToName = .Fields(9)
sSubject = .Fields(3) & " " & .Fields(4)
sMessageBody = "Dear Delegate " & vbCrLf & _
vbCrLf & _
vbCrLf & _
"This email is confirmation of your place on the following course:" & _
vbCrLf & _
vbCrLf & _
"" & .Fields(3) & _
vbCrLf & _
"" & .Fields(4) & _
vbCrLf & _
"The course Trainer is: " & .Fields(17) & _
vbCrLf & _
vbCrLf & _
"The Course Venue is:" & _
vbCrLf & _
vbCrLf & _
"" & .Fields(11) & vbCrLf & _
"" & .Fields(12) & vbCrLf & _
"" & .Fields(13) & vbCrLf & _
"" & .Fields(14) & vbCrLf & _
"" & .Fields(15) & vbCrLf & _
"" & .Fields(16)

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

This works well enough, however, FIELDS 11 through to 16 contain the venue address. This is all we ll and good if every field of the venue address is populated. here are times when not all of the fields are populated, for instance, the address might only be 5 lines.

I know I can do this using IIf statements on a report, but how can i achieve the same thing for the email.

 

[pbaldy]

I use this little trick:

  Me.txtCustName = (Me.CustAttn + vbCrLf) & _
                   (Me.CustomerName + vbCrLf) & _
                   (Me.CustomerAddress1 + vbCrLf) & _
                   (Me.CustAddress2 + vbCrLf) & _
                   (Me.CustAddress3 + vbCrLf)

 

[mousemat]

Will that not produce the same result?

The fields are as followws:

VenueName
VenueAddressL1
VenueAddrssL2
VenueTown
VenueCounty
VenuePostcode.

Not all addresses will have a VenueAddressL2 field.

If thats the case, I want the resultant email to look like this:

VenueName
venueAddressL1
VenueTown
VenueCounty
VenuePostcode

On a report, I can format it thus:

=[VenueName] & Chr(13) & Chr(10) & [VenueAddressL1] & Chr(13) & Chr(10) & IIf(IsNull([VenueAddressL2]),"",[VenueAddressL2] & Chr(13) & Chr(10)) & IIf(IsNull([VenueTown]),"",[VenueTown] & Chr(13) & Chr(10)) & IIf(IsNull([VenueCounty]),"",[VenueCounty] & Chr(13) & Chr(10)) & IIf(IsNull([VenuePostCode]),"",[VenuePostCode])

I can't seem to see how to do it for the email.

 

[pbaldy]

You didn't try it, did you? I took advantage of the fact that "+" propagates Nulls, and "&" does not. Thus if customer address 2 is null, there won't be an empty line.

 

[mousemat]

I must admit, I didnt try it, I cant seem to work it in with my code.

 

[mousemat]

BINGO!!!!!

I see it all now, works a treat.

Many thanks for that

 

[pbaldy]

Happy to help!