Email Workorder Data

[andrewneal]

I love all the email post information that is available, but I haven't been able to find exactly what I am looking for...

Suppose a customer enters a computer workorder request. Then upon completing the form, your boss wants it to automatically send your office an email "your.office@here.com" and the requester's email (which it would get from email field the customer just entered their request into). The boss wants it to also include in the subject line "Workorder #" and then the workorder autonumber that was assigned to that workorder request. The boss also wants certain information inside the body of the email... ProblemType, Description, Remarks & LastName.

This should be sent automatically without the "customer" knowing or having to interact with outlook in anyway (if possible).

Thanks!

 

[Moniker]

Outlook needs to at least be open, otherwise the user will get a message that says something like "A program is trying to access the Outlook Address Book. Some viruses may attempt to do this. Do you want to continue? If you are unsure, select No." Nine out of ten users will click No regardless of how many times you tell them to click Yes.

In the VBA Code Window, add the Outlook reference "Microsoft Outlook X.0 Object Library" (where X is the version number, 11 for Office 2003).

Here's how to do what you want (put this in the click event of the "Email" button):

    Dim OLApp As New Outlook.Application
    Dim OLMsg As Outlook.MailItem
    
    Set OLMsg = OLApp.CreateItem(olMailItem)
    With OLMsg
        .Recipients.Add "your_email_to_person@their_email_address"
        .Subject = "What you want in the subject line"
        .Attachments.Add Name_of_file_to_attach_if_you_need_to_attach_a_file
        .Body "The_body_text_of_the_email"
        .Send
    End With
    
    MsgBox "Emailing Complete", vbOKOnly + vbInformation, "Email Sent"
    
    Set OLApp = Nothing
    Set OLMsg = Nothing

That's all there is to it.

 

[andrewneal]

This is great... Our users have Outlook open all the time anyway. I can put a pop-up message at the beginning of the request form telling them they need to have Outlook open to complete the request...just incase.

The goal is for the items below in red to be pulled automatically from the request form "Request_frm"...

------------------------------

To: IT.email@email.me

CC: Customer.email@email.me

Subject: Workorder #XXXX

Message Body:

You should keep this email for your records until the request is completed...

Workorder Number: XXXX
Customer: XXXX
Computer Name: XXXXX

------------------------------

From the posts I have read, I am guessing it would be something like:

With OLMsg
.Recipients.Add "IT.email@email.me"
.Recipients.Add [Forms]![Request_frm]!
.Subject = "Workorder # " & [Forms]![Request_frm]![Workorder_Number][/COLOR]

How far off base am I with this?

 

[Moniker]

That will work just fine. Note that both recipients will end up in the "To" field, not one in the "CC" field, but that's really nothing as the email will go to all the appropriate recipients anyway.

Also, for the ".Body" part, you may want to make a string out of it. At the top of the sub, put in Dim strBody As String. Then do something like this:

strBody = "You should keep this email for your records until the request is completed..." & vbCrLf & vbCrLf
strBody = strBody & "Workorder Number: " & [workorder_number_field] & vbCrLf
strBody = strBody & "Customer: " & [customer_field] & vbCrLf
strBody = strBody & "Computer Name: " & [computer_name_field] & vbCrLf
.Body = strBody

 

[andrewneal]

After some configuring and error checks, I have come up with the following code which is associated with the click function of the "Submit" button. I also loaded the Outlook Object Library 11x as you suggested.

Private Sub EMAIL_THIS_Click()

Dim OLApp As New Outlook.Application
Dim OLMsg As Outlook.MailItem
Dim stDocName As String
Dim strBody As String

Set OLMsg = OLApp.CreateItem(olMailItem)
With OLMsg
.Recipients.Add [Forms]![Request_frm]!
.Recipients.Add [Forms]![HDEmail_frm]![Email]
.Subject = "Trouble Ticket # " & [Forms]![Request_frm]![ID]

strBody = "YOU SHOULD KEEP THIS EMAIL FOR YOUR RECORDS UNTIL THIS REQUEST IS RESOLVED..." & vbCrLf & "-------------------------------------------------------------------------------" & vbCrLf & vbCrLf & "Ticket Number: " & [Forms]![Request_frm]![ID] & vbCrLf & "Date Submitted: " & [Forms]![Request_frm]![Date] & vbCrLf & "Customer: " & [Forms]![Request_frm]![Rank] & " " & [Forms]![Request_frm]![LName] & vbCrLf & "Customer's Phone: " & [Forms]![Request_frm]![Phone] & vbCrLf & "Customer's Computer Name: " & [Forms]![Request_frm]![ComputerName] & vbCrLf & vbCrLf & "Problem: " & [Forms]![Request_frm]![ProblemType] & vbCrLf & "Description: " & [Forms]![Request_frm]![Description] & vbCrLf & vbCrLf & "Thank You! We will be in touch with you soon!"

.Body = strBody

.Send

End With

MsgBox "Emailing Complete", vbOKOnly + vbInformation, "Email Sent"

Set OLApp = Nothing
Set OLMsg = Nothing

End Sub[/COLOR]

Now I get the warning that another program is sending as Outlook... Any suggestions? Otherwise, it works perfectly!

 

[Moniker]

That's a security thing. Go to Tools -> Macro -> Security and set the security to low. Unfortunately on this latest go-round, Microsoft sort of assumed that all users were idiots and you just have to lower the security to resolve this. You can also set the security programmatically if you'd like to keep it at Medium or whatever setting it's currently at. In other words, when you load the DB, have some code that checks the security and set it to low that way.

Also note that without the MS Outlook reference, you couldn't use this:

Dim OLApp as New Outlook.Application

Outlook would not appear in the drop-down list of object types.

 

[andrewneal]

What code would be able to be loaded to get by this security? It might be better to have an email actually pop up and the user just needs to hit the send button on the email, but I am not sure how to get that to happen. The best bet would be to code the ability to bypass this security...

 

[Moniker]

Look into this command:

Application.AutomationSecurity

You'll need the Microsoft Office X.0 Object Library to set it (just add it in References).