Generate Email with formatting issue

[MichelleB]

Hi i have a script that creates an email for a form with general formatting, but i need to format the date to dddd dd mmmm yyyy and the time i need to be hh.mm am/pm, how do i do this please in this script

Private Sub Command208_Click()
Dim msgTxt As Variant
Dim objOutlook As Outlook.Application
Dim objMailItem As Outlook.MailItem
Dim blnCreated As Boolean
Dim act As String
Dim venue As String
Dim gigdate As String
Dim start As String
Dim finish As String
Dim actfee As String
Dim netpay As String
Dim actpayment As String
Dim actcomment As String

strEmail = "test@here.com"
strSubject = "New Booking Confirmation"
strBody = "<h2><b>A new booking has been confirmed for</b></h2>"
act = Me.artist
Date = Me.gigdate
venue = Me.venuename
start = Me.start
finish = Me.finish
actfee = Me.actfee
netpay = Me.netpay
commission = Me.commission
actpayment = Me.actpayment


blnCreated = False

Set objOutlook = New Outlook.Application

If IsNull(Me.email) = True Or Me.email = "" Then
DoCmd.Hourglass False
msgTxt = MsgBox("Unable to create an email for " & Me.act & Chr(13) & "No email address listed in the database.", vbOKOnly + vbInformation, "")
Else
DoCmd.Hourglass True
Set objMailItem = objOutlook.CreateItem(olMailItem)

With objMailItem
.To = Me.email
.CC = strEmail
.Subject = strSubject & " " & act & " on " & gigdate & " at " & venue
.HTMLBody = strBody & "<p>" & "<p>" & act & "<br>" & Format([gigdate], "m d yyy") & "<br>" & venue & "<br>" & start & " - " & finish & "<br>" 


.Save
' .Send
blnCreated = True
End With

End If

If blnCreated Then
msgTxt = MsgBox("Finished creating email(s). The email(s) are in your Outlook 'Drafts' folder.", vbOKOnly, "")
Else
msgTxt = MsgBox("Email Failed.", vbOKOnly, "")
End If

DoCmd.Hourglass False
End Sub

 

[Beetle]

Just format the value when you assign it to your variables (I'm assuming here that start and finish are your time values);

strEmail = "test@here.com"
strSubject = "New Booking Confirmation"
strBody = "<h2><b>A new booking has been confirmed for</b></h2>"
act = Me.artist
[COLOR="Blue"]gigdate = Format(Me.gigdate, "dddd dd mmmm yyyy")[/COLOR]
venue = Me.venuename
[COLOR="blue"]start = Format(Me.start, "hh:nn am/pm")
finish = Format(Me.finish, "hh:nn am/pm")[/COLOR]
actfee = Me.actfee
netpay = Me.netpay
commission = Me.commission
actpayment = Me.actpayment

BTW - I'm guessing that the following line from the code you posted;

Date = Me.gigdate

is just a typo in your post? If not you should correct it as Date is an Access/VBA reserved word and should not be used as the name of a user defined object or variable.

 

[MichelleB]

thankyou this worked perfectly and thanks for picking up date use error