troubleshooting Question

VBA Code To Run In Selected Email

Avatar of Geekamo
GeekamoFlag for United States of America asked on
Visual Basic ClassicOutlookVB Script
11 Comments1 Solution433 ViewsLast Modified:
Hello Experts,

Please bare with me as I explain this one...

(VBA Code is at the bottom)

In Outlook, I have a special button to execute some VBA code.  When the user does so - they are presented with a UserForm, in which they pick what action they want to do.

For example...

All of the actions are, email templates - which are all sent off to our Customer Service Department, preformatted per the specific request.

Request Type
Here is the R/A Request template - showing all the fields that need data.  And you'll also see the "Generate Email" button.

RA Request
When the user clicks on "Generate Email" - the code behind the scenes executes, and dumps all the form data into an email just like this...

Email Output
I really have no complaints, other than - I'm stuck with it being a brand new email every time.

There are going to be situations, where the user receives an email from a customer - and wants to forward it along to our Customer Service Department.  Basically, keeping the history trail of that email thread.

Personally, I don't mind the idea of having a brand new email each time - (from my code) because I can easily, drag whatever supporting emails I need, into the newly created email from my code.

But I am pretty sure, some people will be making a stink to this.

Ideally, what I would like is this...

(Mind you, all of what I ask - I will need help with, I am VERY new to VBA and get lost easily...)

1.)  When code is executed - it checks to see what email is selected in the "Inbox" and then remembers that selected email.
2.)  A window pops up, asking the question...

What you like to create a new email, or forward currently selected email?

Options:  New / Forward

3.)  My template window shows up, (first image pictured).  User selects what template they want.
4.)  User fills out template like normal and clicks "Generate Email".

Here's the tricky part...

If the user selected "New", then my code should run as normal, outputting all data into a brand new email like 3rd image pictured above.

But if the user selected "Forward", then the code needs to look at the actively selected email in the users inbox, preform a forward, execute my code to output all the data that was collected at the TOP of the email thread - and still address it and populate the subject.

Does that make sense?

If you have any questions, please let me know.

Thanks,
Geekamo

Option Explicit
Private Sub userform_activate()
     
    Dim i As Integer
    
    Dim cb

    For i = 1 To 5

        Set cb = Controls("cboReason" & i)

        cb.AddItem "Apple"
        cb.AddItem "Egg"
        cb.AddItem "Bread"
        cb.AddItem "Cheese"
        cb.AddItem "Milk"

    Next
    
End Sub

Private Sub GenerateEmail_Click()

'CLEAN UP

    Unload RARequest
    
'CREATE VARIABLES

    Dim mai As MailItem
    Dim Reason(1 To 5) As String
    Dim i As Integer
    Dim strText, RequestNotes As String
    Dim CustNum, CustName, ContNum, ContName As String
    Dim OrderNum, Reason1, Reason2, Reason3, Reason4, Reason5 As String
    
'POPULATE VARIABLES

    CustNum = txtCustNum.Value
    CustName = StrConv(txtCustName.Value, vbUpperCase)
    ContNum = txtContNum.Value
    ContName = StrConv(txtContName.Value, vbUpperCase)
    
    RequestNotes = txtNotes.Value
    
    OrderNum = txtOrderNum.Value
    
    Reason(1) = cboReason1.Value
    Reason(2) = cboReason2.Value
    Reason(3) = cboReason3.Value
    Reason(4) = cboReason4.Value
    Reason(5) = cboReason5.Value
    
'COMPILE EMAIL BODY

    strText = "<b><u>R/A REQUEST</b></u>"
    strText = strText & "<br><br>"
    strText = strText & "<b><u>CUSTOMER INFORMATION</b></u>"
    strText = strText & "<br><br>"
    strText = strText & "<b>Customer: </b>" & ContNum & " | " & ContName
    strText = strText & "<br>"
    strText = strText & "<b>Contact: </b>" & ContNum & " | " & ContName
    strText = strText & "<br><br>"
    strText = strText & "<b><u>ORDER INFORMATION</b></u>"
    strText = strText & "<br><br>"
    strText = strText & "<b>Order #: </b>" & OrderNum
    strText = strText & "<br><br>"

    For i = 1 To 5
        If Reason(i) <> "" Then 'or <> "" Then
            strText = strText & "<b>Reason " & i & "</b>" & " - " & Reason(i)
            strText = strText & "<br>"
        End If
    Next i

    strText = strText & "<br>"
    strText = strText & "<b><u>ADDITIONAL INFORMATION</b></u>"
    strText = strText & "<br><br>"
    strText = strText & RequestNotes
    strText = strText & "<br><br>"
    
'EMAIL OUTPUT

    Set mai = Application.CreateItem(olMailItem)
    With mai
        .To = "geekamo@me.com"
        .CC = "geekamo@me.com"
        .Subject = "R/A (RQ) | " & CustNum & " - " & CustName & " | " & ContNum & " - " & ContName
        .HTMLBody = "<p style='font-family:calibri'>" & strText & "</p>"
        .Display
    End With

End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros