Hi all,
I am trying to add a feature to our database that automates sending emails.
When entering a new order, I want the option to send an email to the company the order came from, attaching a copy of our work order.
The file that I want to attach is a rich text document created by Access and stored on our server. The creation of this document is not the problem.
The problem is getting the email option to work. It does not seem to work. In fact, it just does nothing at all, not even an error message.
The following code is just part of the VBA for an On Click event for a command button on my Add New Orders form. The button click does several things, all of which work fine. I tried to add the email option to the list of events that occur on that button click.
Let me know if you will need to see the entire code for that button click.
"SC_NEW" is control source table for the "Add New Orders" form
"ORDER" is the field on that table where we enter the company that sent us an order
"CASE_N" is the field on that table where we enter the ordering company's case number.
"FILE_NO" is a field for an automatically generated File Number (not the same as the case number)
"cust" is a table where we store contact information for regular customers
"cusname" is the field that stores the company's name
"email" is the field (hyperlink field) that stores an email address for that company (is the fact that it's a hyperlink field the problem, maybe?)
The File Number has already been generated, and the files on the server have already been created as a part of the On Click event, prior to reaching this part of the code.
I think I have covered everything. Let me know if I forgot to define anything.
Note: I want the email to open up for editing before sending.
Thanks!
I am trying to add a feature to our database that automates sending emails.
When entering a new order, I want the option to send an email to the company the order came from, attaching a copy of our work order.
The file that I want to attach is a rich text document created by Access and stored on our server. The creation of this document is not the problem.
The problem is getting the email option to work. It does not seem to work. In fact, it just does nothing at all, not even an error message.
The following code is just part of the VBA for an On Click event for a command button on my Add New Orders form. The button click does several things, all of which work fine. I tried to add the email option to the list of events that occur on that button click.
Code:
' Email New Order to Recipient
' Yes No Box
Msg = "Do you want to EMAIL a confirmation of this order?" ' Define message.
Style = vbYesNo ' Define buttons.
Title = "Email Confirmation" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim stRecip As String
If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
stRecip = ""
Else
stRecip = DLookup("email", "cust", "[cusname]='" & Me!ORDER & "'")
End If
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(stRecip)
objOutlookRecip.Type = olTo
' Set the Subject and Body of the message.
.Subject = "Survey Order Receipt Confirmation (" & Me.CASE_N & ")"
.Body = "This email is to confirm that we have entered your recent survey request into our system. Please review the attached work order, and let us know if you find any errors. Thank you!" & vbCrLf & vbCrLf
' Add attachments to the message.
If Not IsMissing("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf") Then
Set objOutlookAttach = .Attachments.Add("O:\Orders_" & yr & "\" & [FILE_NO] & "\" & [FILE_NO] & ".rtf")
End If
' Display email
objOutlookMsg.Display
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
End If
"SC_NEW" is control source table for the "Add New Orders" form
"ORDER" is the field on that table where we enter the company that sent us an order
"CASE_N" is the field on that table where we enter the ordering company's case number.
"FILE_NO" is a field for an automatically generated File Number (not the same as the case number)
"cust" is a table where we store contact information for regular customers
"cusname" is the field that stores the company's name
"email" is the field (hyperlink field) that stores an email address for that company (is the fact that it's a hyperlink field the problem, maybe?)
The File Number has already been generated, and the files on the server have already been created as a part of the On Click event, prior to reaching this part of the code.
I think I have covered everything. Let me know if I forgot to define anything.
Note: I want the email to open up for editing before sending.
Thanks!
Comment