showing checkbox in an email

[iankerry]

Hi All

I have a button on my form which produces an email to a person with all their event bookings on it:-

Dear xxxxx,
Below are your requested bookings. Please check all the details and read to the end of this email:
Date: 20/11/2010
Film: The Disappearance of Alice Creed
Time: 7.30pm
Venue: Clun Memorial Hall
Adult Ticket Price: £4
Child Ticket Price: £2.5
Family Ticket Price: £0
You are going to provide your own dvd: True

The last line is foxing me - i would like it to show a checkbox that is either ticked or not depending on the value of the [owndvd] field, currently stored as a BIT datatype in sql server.

the code so far is:
Private Sub cmdEmail_Click()


Dim arrSummaryData() As SUMMARY_DATA
Dim SQL As String
Dim strEmail As String
Dim strTime As String
Dim tmpowndvd As Boolean
Dim strPromoterName As String
Dim intRecordCount As Integer
Dim dblRecord As Double
Dim i As Integer
Dim lngPromoterId As Long

If IsNull(Me.PromoterID.Value) Then
lngPromoterId = 0
Else
lngPromoterId = Me.PromoterID.Value
End If

intRecordCount = 0
strPromoterName = ""
strEmail = ""
SQL = ""

SQL = "SELECT dbo_EventsFlicks.datefield, dbo_Films.[film name], dbo_EventsFlicks.owndvd, dbo_EventsFlicks.time, dbo_EventsFlicks.AdultTP, dbo_EventsFlicks.FamilyTP, dbo_EventsFlicks.ChildTP, dbo_Venues.VENUE, dbo_Promoters.NAME,dbo_Promoters.email, dbo_Promoters.ID" & _
" FROM (dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN (dbo_Venues INNER JOIN (dbo_Promoters INNER JOIN dbo_EventsFlicks ON dbo_Promoters.ID = dbo_EventsFlicks.promoterID) ON dbo_Venues.ID = dbo_EventsFlicks.venueID) ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID " & _
" WHERE (((dbo_EventsFlicks.datefield) > Now()) And ((dbo_Promoters.ID) = " & lngPromoterId & "))" & _
" ORDER BY dbo_EventsFlicks.datefield"
If OpenConnection Then
If rs.State = adStateOpen Then rs.Close
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

If Not rs.EOF Then
dblRecord = rs.RecordCount
ReDim arrSummaryData(1 To dblRecord)
'create an Array depends on number of records and put all data with features
'
With rs
intRecordCount = 1
Do While Not .EOF
arrSummaryData(intRecordCount).strPromoterName = IIf(IsNull(![NAME]), "", ![NAME])
strPromoterName = arrSummaryData(intRecordCount).strPromoterName
arrSummaryData(intRecordCount).strEmail = IIf(IsNull(!), "", ![email])
strEmail = arrSummaryData(intRecordCount).strEmail
arrSummaryData(intRecordCount).dtDatefield = IIf(IsNull(![datefield]), "", ![datefield])
arrSummaryData(intRecordCount).strTime = IIf(IsNull(![time]), "", ![time])
If owndvd = 0 Then tmpowndvd = False
If owndvd = -1 Then tmpowndvd = True
'tmpowndvd = arrSummaryData(intRecordCount).tmpowndvd
arrSummaryData(intRecordCount).strFilmName = IIf(IsNull(![film name]), "", ![film name])
arrSummaryData(intRecordCount).strVenue = IIf(IsNull(![VENUE]), "", ![VENUE])
arrSummaryData(intRecordCount).curAdultTP = IIf(IsNull(![AdultTP]), "", ![AdultTP])
arrSummaryData(intRecordCount).curChildTP = IIf(IsNull(![ChildTP]), "", ![ChildTP])
arrSummaryData(intRecordCount).curFamilyTP = IIf(IsNull(![FamilyTP]), "", ![FamilyTP])
.MoveNext
intRecordCount = intRecordCount + 1
Loop
.Close
End With

Dim strMessage As String

strMessage = "Dear " & strPromoterName & "," & vbCrLf & vbCrLf & "Below are your requested bookings. Please check all the details and read to the end of this email:" & vbCrLf

If Len(strEmail) = 0 Then
strEmail = "terri@artsalive.co.uk"
End If

For i = LBound(arrSummaryData) To UBound(arrSummaryData)
strMessage = strMessage & vbCrLf & "Date: " & arrSummaryData(i).dtDatefield & " " & vbCrLf & "Film: " & arrSummaryData(i).strFilmName & " " & vbCrLf & "Time: " & arrSummaryData(i).strTime & " " & vbCrLf & "Venue: " & arrSummaryData(i).strVenue & " " & vbCrLf & "Adult Ticket Price: £" & arrSummaryData(i).curAdultTP & " " & vbCrLf & "Child Ticket Price: £" & arrSummaryData(i).curChildTP & " " & vbCrLf & "Family Ticket Price: £" & arrSummaryData(i).curFamilyTP & " " & vbCrLf & "You are going to provide your own dvd:" & " " & tmpowndvd & vbCrLf
Next
strMessage = strMessage & vbCrLf & "I hope the above booking details are correct, please let me know if you need to make any amendments. Regards" & vbCrLf

Call SendAttachments("Booking information", strMessage, strEmail)
Else
MsgBox " There are no records for that Promoter.", vbInformation
End If
End If

CleanExit:

If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close
End If
Exit Sub

End Sub




anyone any ideas?

Thanks

ian

 

[boblarson]

This is not a simple request. What you would need is to be able to use HTML formatted email and you would provide the checked box or not using a special character from the WingDings character set. So your email message would essentially need to be written like a web page using HTML and the value of tmpowndvd would need to checked and the appropriate symbol substituted (either 0xFE or 0xA8).

 

[iankerry]

Hi Bob

Thanks for replying. No wonder that i couldn't get it to work as it wasnt simple!

Perhaps I'll just change it to a yes or no field. I just wanted it to stand out more as it was a crucial piece of info.

Is there at least a way of making the field value bold? my emails are HTML anyway.

best wishes
ian

 

[boblarson]

Is there at least a way of making the field value bold? my emails are HTML anyway.

best wishes
ian

You need to reformat your code to use HTML instead. I can tell it isn't currently doing that as you have vbCrLf in there and you would need to use <BR> instead.

So, you would need to change something like this:

strMessage = "Dear " & strPromoterName & "," & [COLOR=red][B]<BR> & <BR> [/B][/COLOR]& "Below are your requested bookings. Please check all the details and read to the end of this email:" & [COLOR=red][B]<BR>[/B][/COLOR]

strMessage = strMessage & [COLOR=#ff0000]<BR>[/COLOR] & "Date: " & arrSummaryData(i).dtDatefield & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Film: " & arrSummaryData(i).strFilmName & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Time: " & arrSummaryData(i).strTime & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Venue: " & arrSummaryData(i).strVenue & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Adult Ticket Price: £" & arrSummaryData(i).curAdultTP & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Child Ticket Price: £" & arrSummaryData(i).curChildTP & " " & [COLOR=#ff0000]<BR>[/COLOR] & "Family Ticket Price: £" & arrSummaryData(i).curFamilyTP & " " & [COLOR=red]<BR>[/COLOR] & "You are going to provide your own dvd:" & " " & [COLOR=red][B]<B> & [/B][/COLOR]tmpowndvd & [B][COLOR=red]</B>[/COLOR][/B] & <BR>

And then under SEND ATTACHMENTS you might need to specify explicitly that you want HTML email.

 

[iankerry]

Thanks Bob, appreciate your time.

Ian

 

[darbid]

I did not know what wingdings were now I do.

I was just looking at your old thread.

by the way this would work

</a>You are going to provide your own dvd:   <a>
<span style="font-family: wingdings; font-size: 250%;">ü</span>

 

[boblarson]

I did not know what wingdings were now I do.

I was just looking at your old thread.

by the way this would work

</a>You are going to provide your own dvd:   <a>
<span style="font-family: wingdings; font-size: 250%;">ü</span>

That I don't know. You would have to try it to see.

 

[darbid]

I'm not asking the question. I know it works

 

[boblarson]

I'm not asking the question. I know it works

Ah, sorry - got the words mixed up. I thought it was

by the way would this work

instead of what you really wrote:

by the way this would work

Glad it's Friday.