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
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