Auto email question 2

[LD1010]

Thanks to Nathan and his faq702-2921 I was able to automate the sending of email messages from a command button cmdSendEmail on a subform called sfmMilestoneEntryEmail. I have now been asked to change this so that the email message is automatically sent based on the On Exit Event of a control called DateCompleted on the main form called frmMilestoneEntry. Can anyone tell me how to go about this. Writing code is a new adventure for me. Thanks in advance!!

Here is the code I have for the command button.

Private Sub cmdSendEmail_Click()
Dim email As String
Dim CCEmail As String
Dim ref As String
Dim notes As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from subform and sets the string variable to the fields
email = Me.EmailNotice
CCEmail = Me.CCEmailNotice
ref = Me.EmailRef
notes = Me.EmailBody

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = email
.CC = CCEmail
.Subject = ref
.Body = notes
.Send
End With

'**closes outlook
objOutlook.Quit
Set objEmail = Nothing

Exit Sub

End Sub

 

[GingerR]

Copy all of the code between the PRIVATE SUB and END SUB statements. In the On Exit event of the combo box, paste the code in there. Since you are launching from the "main" form instead of the subform, you have to tweak these statements:

email = Me.sfmMilestoneEntryEmail!EmailNotice
CCEmail = Me.sfmMilestoneEntryEmail!CCEmailNotice
ref = Me.sfmMilestoneEntryEmail!EmailRef
notes = Me.sfmMilestoneEntryEmail!EmailBody

This is assuming that the email info you want to use as parameters are selected in the subform.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TheAceMan1]

How are ya LD1010 . . .

In the [blue]On Exit[/blue] event:

[blue]   Call cmdSendEmail_Click[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[LD1010]

Thanks GingerR, worked like a charm. I appreciate the help.

TheAceMan1, thanks for responding. Actually I did try what you have suggested, calling the code on the command button. But when I did I got a compile error: Sub or Function not defined. I didn't know what to do at that point, thus promting my post.

 

[Crowley16]

to get the aceman's solution to work, first make the cmdSendEmail_Click function public, change the private to public at the front of that line of code.

then use sfmMilestoneEntryEmail.cmdSendEmail_Click

--------------------
Procrastinate Now!

 

[LD1010]

Thanks Crowley16, I'll go back and give that a try. I appreciate your help!!

 

[TheAceMan1]

Howdy Crowley16 . . .

[blue]Private: Indicates that the procedure is accessible only to other procedures in the module where it is declared ([purple]same module[/purple]).[/blue]

Meaning . . . [blue]any procedure can call any [purple]private procedure[/purple], as long as they both reside in the same module[/blue] . . . no need to revert to public.

See Ya! . . . . . .

Be sure to see thread181-473997