Hello, Fellow Users,
I need a brave soul (or 5) to peruse this de-sensitized button code for any obvious or unobvious errors. In the VB editor, there are no red “error” lines of text...but I haven’t run the compiler yet. I tried to keep it as simple as possible given the scope. As you’ll see, there are some questions tagged in text within the code itself. The Db in question has one form and 3 tables. The button is on the form and is currently source controlled to only one table. The one form control that uses a SELECT/FROM statement to populate the form with a movie code is the only one that’s unbound. This version has back-to-back commands referring to one recordset, though I’d like to try another sequence - this is explained at the bottom of this post. The steps the button is to execute with back-to-back same recordset commands are as follows:
1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
Note: In steps 7 & 8, I have the two commands that deal with the recordset/”Unused” table one-following-the-other so as to not have to close the recordset and then re-open it.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it.
8. While recordset/”Unused” table is still “active”, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed.
9. Access populates the “movie code” text in .oft template.
10. Outlook .oft template is send as message. End Sub.
------------------------------------------------------
Private Sub AS1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()
' Re-send only employee Outlook e-mail populated with Access data using Outlook .oft template by clicking button. Button code will draw on data in controls of RECORD SHOWING.
' Used GetObject method rather than CreateItemFromTemplate method for simplicity.
' Namespace/MAPI commands not used as some employees (i.e. Animation) are on mail client other than Outlook.
' On button click but before automation begins, record will be saved to table but REMAIN ONSCREEN (not go to new, blank record). This first line of code does that...
DoCmd.RunCommand acCmdSaveRecord
' Now, the automation begins. Declaration statement(s)...
Dim objOutlookMsg As Object
' This next line opens Outlook by retrieving employee welcome e-mail template...
' ,Class needed in pathname or not?
objOutlookMsg = GetObject("J:\Special Projects\Database Work\AS1 Tracking DB & Related\AS1 Form Button Automation Email\Employee AS1 Welcome Outlook Template.oft")
' These next lines check the "Known As" data in the record, and if it's not null, populate the "To:" field in email; if it's null, "First Name" data in record should populate "To:" field instead.
objOutlookMsg.To = Replace("<<Known As>>", "<<Known As>>", "[AS1 Onboarding Tracking Table]![Known As]")
If IsNull("[AS1 Onboarding Tracking Table]![Known As]") Then
ReplaceNull = ("[AS1 Onboarding Tracking Table]![First Name]")
End If
' This next line populates the mail's "From" line from data in the "HR EOD Contact Name" form control.
objOutlookMsg.From = ("[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
' These next lines auto-fill the mail subject with boilerplate...
objOutlookMsg.Subject = "Congratulations and Welcome To XXXXXXXXX!"
' Body is almost all boilerplate (only HR EOD contact and movie code sections need populating).
objOutlookMsg.BodyFormat = olFormatRichText
objOutlookMsg.Body = Replace("<<HR EOD Contact Name>>", "<<HR EOD Contact Name>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
objOutlookMsg.Body = Replace("<<HR EOD Contact Internal Phone #>>", "<<HR EOD Contact Internal Phone #>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal Phone #]")
objOutlookMsg.Body = Replace("<<HR EOD Contact Internal E-Mail>>", "<<HR EOD Contact Internal E-Mail>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal E-Mail]")
Exit Sub
' These next lines copy the top movie code (sorted ascending) from "Unused Movie Code Table" and populate "Movie Code" control on form.
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "WHERE [Movie Code] = Me.[Movie Code]" & "ORDER BY Movie Code ASC")
rs![Movie Code] = Me.[Movie Code]
‘ Is this line needed in this case?
rs.Update
' This next line then moves (not copies) that movie code from "Unused Movie Code Table" to "Used Movie Code Table".
CurrentDb.Execute "INSERT INTO [Used Movie Code Table].Movie Code" & "SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "ORDER BY Movie Code ASC"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
' This next line populates <<movie code>> text on template with "Movie Code" control's data on form.
objOutlookMsg.Body = Replace("<<movie code>>", "<<movie code>>", "[AS1 Onboarding Tracking Table]![movie code]")
Exit Sub
' This next line re-sends the welcome e-mail only. User can verify it was sent in mail account's "Sent Items" box.
objOutlookMsg.Send
Set objOutlookMsg = Nothing
End Sub
--------------------------------------
Aesthetically, I would like to do the last population on the Outlook template and get it sent off before I move the code I populated the form and template with from the “Unused” table to the “Used” table. Moving this code last would also help the user avoid any manual digging in the tables for the movie code should the code freeze and fail to populate the form control anytime after the automation button is clicked. Here is the sequence I really, really want. (Basically steps 9 & 10 in the above configuration are put between steps 7 & 8). Is it a bad idea to close the recordset, send the mail off, then open the recordset again to move the used code? Is it even possible? In coding terms, would that be too messy/buggy?
1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it. Recordset is closed.
8. Access populates the “movie code” text in .oft template from form control data.
9. Outlook .oft template is send as message.
10. Recordset/”Unused” table is re-opened with db.OpenRecordset function, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed a second time. End Sub.
Thanks so much for your time in reviewing this. Any sure-thing advice would be appreciated, especially from intermediate and expert VB forum users. I want the code to be as simple as possible. Feel free to highlight/notate the heck out of it.
Frank
I need a brave soul (or 5) to peruse this de-sensitized button code for any obvious or unobvious errors. In the VB editor, there are no red “error” lines of text...but I haven’t run the compiler yet. I tried to keep it as simple as possible given the scope. As you’ll see, there are some questions tagged in text within the code itself. The Db in question has one form and 3 tables. The button is on the form and is currently source controlled to only one table. The one form control that uses a SELECT/FROM statement to populate the form with a movie code is the only one that’s unbound. This version has back-to-back commands referring to one recordset, though I’d like to try another sequence - this is explained at the bottom of this post. The steps the button is to execute with back-to-back same recordset commands are as follows:
1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
Note: In steps 7 & 8, I have the two commands that deal with the recordset/”Unused” table one-following-the-other so as to not have to close the recordset and then re-open it.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it.
8. While recordset/”Unused” table is still “active”, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed.
9. Access populates the “movie code” text in .oft template.
10. Outlook .oft template is send as message. End Sub.
------------------------------------------------------
Private Sub AS1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()
' Re-send only employee Outlook e-mail populated with Access data using Outlook .oft template by clicking button. Button code will draw on data in controls of RECORD SHOWING.
' Used GetObject method rather than CreateItemFromTemplate method for simplicity.
' Namespace/MAPI commands not used as some employees (i.e. Animation) are on mail client other than Outlook.
' On button click but before automation begins, record will be saved to table but REMAIN ONSCREEN (not go to new, blank record). This first line of code does that...
DoCmd.RunCommand acCmdSaveRecord
' Now, the automation begins. Declaration statement(s)...
Dim objOutlookMsg As Object
' This next line opens Outlook by retrieving employee welcome e-mail template...
' ,Class needed in pathname or not?
objOutlookMsg = GetObject("J:\Special Projects\Database Work\AS1 Tracking DB & Related\AS1 Form Button Automation Email\Employee AS1 Welcome Outlook Template.oft")
' These next lines check the "Known As" data in the record, and if it's not null, populate the "To:" field in email; if it's null, "First Name" data in record should populate "To:" field instead.
objOutlookMsg.To = Replace("<<Known As>>", "<<Known As>>", "[AS1 Onboarding Tracking Table]![Known As]")
If IsNull("[AS1 Onboarding Tracking Table]![Known As]") Then
ReplaceNull = ("[AS1 Onboarding Tracking Table]![First Name]")
End If
' This next line populates the mail's "From" line from data in the "HR EOD Contact Name" form control.
objOutlookMsg.From = ("[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
' These next lines auto-fill the mail subject with boilerplate...
objOutlookMsg.Subject = "Congratulations and Welcome To XXXXXXXXX!"
' Body is almost all boilerplate (only HR EOD contact and movie code sections need populating).
objOutlookMsg.BodyFormat = olFormatRichText
objOutlookMsg.Body = Replace("<<HR EOD Contact Name>>", "<<HR EOD Contact Name>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
objOutlookMsg.Body = Replace("<<HR EOD Contact Internal Phone #>>", "<<HR EOD Contact Internal Phone #>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal Phone #]")
objOutlookMsg.Body = Replace("<<HR EOD Contact Internal E-Mail>>", "<<HR EOD Contact Internal E-Mail>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal E-Mail]")
Exit Sub
' These next lines copy the top movie code (sorted ascending) from "Unused Movie Code Table" and populate "Movie Code" control on form.
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "WHERE [Movie Code] = Me.[Movie Code]" & "ORDER BY Movie Code ASC")
rs![Movie Code] = Me.[Movie Code]
‘ Is this line needed in this case?
rs.Update
' This next line then moves (not copies) that movie code from "Unused Movie Code Table" to "Used Movie Code Table".
CurrentDb.Execute "INSERT INTO [Used Movie Code Table].Movie Code" & "SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "ORDER BY Movie Code ASC"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
' This next line populates <<movie code>> text on template with "Movie Code" control's data on form.
objOutlookMsg.Body = Replace("<<movie code>>", "<<movie code>>", "[AS1 Onboarding Tracking Table]![movie code]")
Exit Sub
' This next line re-sends the welcome e-mail only. User can verify it was sent in mail account's "Sent Items" box.
objOutlookMsg.Send
Set objOutlookMsg = Nothing
End Sub
--------------------------------------
Aesthetically, I would like to do the last population on the Outlook template and get it sent off before I move the code I populated the form and template with from the “Unused” table to the “Used” table. Moving this code last would also help the user avoid any manual digging in the tables for the movie code should the code freeze and fail to populate the form control anytime after the automation button is clicked. Here is the sequence I really, really want. (Basically steps 9 & 10 in the above configuration are put between steps 7 & 8). Is it a bad idea to close the recordset, send the mail off, then open the recordset again to move the used code? Is it even possible? In coding terms, would that be too messy/buggy?
1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it. Recordset is closed.
8. Access populates the “movie code” text in .oft template from form control data.
9. Outlook .oft template is send as message.
10. Recordset/”Unused” table is re-opened with db.OpenRecordset function, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed a second time. End Sub.
Thanks so much for your time in reviewing this. Any sure-thing advice would be appreciated, especially from intermediate and expert VB forum users. I want the code to be as simple as possible. Feel free to highlight/notate the heck out of it.
Frank