Another Problem with Email and Formatting Text

duthiedon

Donner
Local time
Today, 15:06
Joined
Dec 27, 2007
Messages
60
This forum is incredibly useful! Particularly for individuals who are not experts in MS Access or programming. Any assistance would be GREATLY appreciated!

Have another problem with a training scheduling database, with the auto generation of emails to participants. They use MS Office 2003. Three problems:

1) They would like the introduction text to be bold and in red text. Have tried several things based on research I could find, but I can't seem to get it to work. The fields in the code would be called "stInviteTextIntroEnglish" and "stInviteTextIntroFrench".

2) When creating the body text of the email, there's some headers such as "When & Where:" that also needed to be bolded and underlined and in blue text.

3) Right now the code will only send one email to one participant. Need to find a way to loop and store all of the participants emails and save them to create only one email.

The code right now works to generate a basic email, now have to work on the final steps.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim varTo As String '-- Address for SendObject
Dim Emailsingle As String '-- initial emails
Dim stCourseDesc As String '--- course description
Dim stCourseDescFrench As String '--- course description french
Dim stPrerequiste As String '-- prerequesites
Dim stPrerequisteFrench As String '-- prerequesites french
Dim stSubject As String '-- Subject line of e-mail
Dim stStartDate As String '-- The start date of the course
Dim stStartDateFrench As String '--- The start date of the course in French
Dim stStartTime As String '-- The start time of the course
Dim stLocation As String '-- the facility
Dim stLocationFrench As String '-- the facility in French
Dim stCourseName As String '-- the course name
Dim stCourseNameFrench As String '--- The course name in french
Dim stInviteTextIntroEnglish As String '--- the intro message
Dim stInviteTextIntroFrench As String '--- the intro message in French
Dim stInviteTextFewMoreThings As String '-- the end message
Dim stInviteTextFewMoreThingsFrench As String '-- the end message
Dim StTextEnglish As String '--- the body text
Dim StTextFrench As String '---- the body text in french

Dim MyMail As Outlook.MailItem
Dim fso As FileSystemObject
Set fso = New FileSystemObject
varTo = DLookup("[EmailAddresses]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseDesc = DLookup("[CourseDescription]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseDescFrench = DLookup("[CourseDescriptionFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stPrerequiste = DLookup("[Prerequisites]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stPrerequisteFrench = DLookup("[PrerequisitesFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stStartDate = Format(DLookup("[StartDate]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]"), "mmmm dd, yyyy")
stStartDateFrench = DLookup("[FrenchDate]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stStartTime = Format(DLookup("[StartTime]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]"), "hh:mm")
stCourseName = DLookup("[CourseName]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseNameFrench = DLookup("[CourseNameFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextIntroEnglish = DLookup("[InviteTextIntroEnglish]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextIntroFrench = DLookup("[InviteTextIntroFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextFewMoreThings = DLookup("[InviteTextFewMoreThings]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextFewMoreThingsFrench = DLookup("[InviteTextFewMoreThingsFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stLocation = DLookup("[FacilityName]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityAddress]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityCity]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[Province]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stLocationFrench = DLookup("[FacilityNameFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityAddressFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityCity]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[Province]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")

stSubject = "Cours/Course: " & stCourseNameFrench & " / " & stCourseName
StTextFrench = stInviteTextIntroFrench & Chr$(13) & _
"Invitation à assister à la séance : " & stCourseNameFrench & Chr$(13) & _
Chr$(13) & _
stCourseDescFrench & Chr$(13) & _
Chr$(13) & _
stPrerequisteFrench & Chr$(13) & _
Chr$(13) & _
"Date et endroit:" & Chr$(13) & _
"Date: " & stStartDateFrench & Chr$(13) & _
"Heure: " & stStartTime & Chr$(13) & _
"Endroit: " & stLocationFrench & Chr$(13) & _
Chr$(13) & _
"Renseignements supplémentaires:" & Chr$(13) & _
stInviteTextFewMoreThingsFrench & Chr$(13) & _
"--------------------------------------------------"
StTextEnglish = Chr$(13) & _
stInviteTextIntroEnglish & Chr$(13) & _
"Invitation to attend the course: " & " " & stCourseName & Chr$(13) & _
Chr$(13) & _
stCourseDesc & Chr$(13) & _
Chr$(13) & _
stPrerequiste & Chr$(13) & _
Chr$(13) & _
"When & Where:" & Chr$(13) & _
"Date: " & stStartDate & Chr$(13) & _
"Time: " & stStartTime & Chr$(13) & _
"Location: " & stLocation & Chr$(13) & _
Chr$(13) & _
"And a few more things:" & Chr$(13) & _
stInviteTextFewMoreThings & Chr$(13) & _
"--------------------------------------------------"

Set MyOutlook = New Outlook.Application
'Write the e-mail content for sending to assignee

Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BodyFormat = olFormatRichText
MyMail.To = varTo
MyMail.Subject = stSubject
MyMail.Body = StTextFrench & StTextEnglish

MyMail.Display

Me.InviteSent = Date
Set MyMail = Nothing
Exit_Command24_Click:
Exit Sub
Err_Command24_Click:
MsgBox "You are missing information to complete this email (email address, course info, facility info)"
Resume Exit_Command24_Click
 
For 1 and 2, I haven't tried to use

MyMail.BodyFormat = olFormatRichText
MyMail.Body = StTextFrench & StTextEnglish

I have had success with

myMail.HTMLBody = "Your string here, with HTML tags for bold, color, etc"

For 3, this should get you started:

http://www.granite.ab.ca/access/email/recordsetloop.htm

Rather than all the DLookups into the same query, just open a recordset on it, and you can get any value from it. That would be much more efficient.
 
Thanks very much Paul! Greatly appreciated! I think the HTML formatting will probably work best. Have to work on the looping aspect for part 3. You relieved a great deal of frustration!
 
No problem; post back if you get stuck.
 
Well just experimented and certainly the desired results will work with the html formatting, however, there's 2 fields that are memo/text based, that I'd like to extract as is, so the user has control over the format. The only problem appears to be, that because it's recognizing that it's supposed to be html format, it wraps all the content and elminates the carriage returns that user input. Any suggestions on how to avoid this?
 
You can try running the field through a Replace function:

Replace([FieldName],Chr(13) & Chr(10),"<br>")
 
Thanks Paul, we decided to go with html, the user will have to be trained on how to take their text and put into html. What a joy!
 

Users who are viewing this thread

Back
Top Bottom