VBA Code To Run In Selected Email

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

Open in new window

LVL 1
GeekamoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
I cannot see the selection for forward therefore I have assumed a parameter is set by your form that I have called bolForward and is simply a Boolean for the purpose that you can easily add.

Sub a1()
Dim insp As Inspector
Dim mai As MailItem

If Application.Inspectors.Count > 0 Then
    If bolForward Then
        If Application.ActiveInspector.CurrentItem.Class = olMail Then
            Set mai = Application.ActiveInspector.CurrentItem.Forward
            mai.Display
        End If
    End If
End If

If mai Is Nothing Then Set mai = Application.CreateItem(olMailItem)

End Sub

Open in new window


The line mai.display can easily be replaced by your assignment text and then sent instead of displayed.

Chris
0
GeekamoAuthor Commented:
@ chris_bottomley,

Thanks for responding. :)

I'll be honest, I don't understand anything you said - well beyond my skill level with all this...  (I am soooo new to all of this...)

In looking at your code though, I'm not sure it is going to do what I was hoping for.

~ Geekamo
0
Chris BottomleySoftware Quality Lead EngineerCommented:
THe code supplied works fine in isolation but needs to integrate with your code.  You made the statement "If the user selected "New",  ... But if the user selected "Forward","

I couldn't see where this selection is made but 'my' snippet needs to use this information so I assumed a variable called bolForward (true/false and defaults to false) which I assume will exist at line 84 of your code and assuming so then 'my' code replaces your line 84 and then your orig 86 to 90 continue to populate the relevant email.

I did mean to say as it stands the original body will be deleted as it stands ... but can be retained if you want?

chris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GeekamoAuthor Commented:
@ chris_bottomley,

Oh, there's no code for it - because I didn't know how to do the "New" / "Forward" thing...  

Basically everything that I mentioned, is something that I needed.

The more I get involved with VBA, the more I pick it up - I can somewhat read through code and understand what is happening (that's half the battle) - but I'm not at a point where I can code something from scratch, unless I've used the code multiple times.

I just recently got comfortable with loops!  lol

I'll be honest, - I'm still at a loss with your code.  :(

~ Geekamo
0
Chris BottomleySoftware Quality Lead EngineerCommented:
How do you plan on asking the question re whether the existing item is to be forwarded ... because I expected it to be for example a checkbox or drop down on your form.

Chris
0
GeekamoAuthor Commented:
@ chris_bottomley,

I was thinking a msgbox - "Would you like to create a new email, or forward the currently selected email?

Buttons:  "New" and "Forward"

Or better yet,...

msgbox, - "Would you like to compose a new email message?"

Yes = My code runs as normal.
No = New code kicks in and works off of the currently selected email.

Does that make sense?

~ Geekamo
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Same idea ... replaces your line 84 but now already includes your assignment entries for the mail:

Dim insp As Inspector
Dim mai As MailItem

If Application.Inspectors.Count > 0 Then
    If Application.ActiveInspector.CurrentItem.Class = olMail Then
        bolForward = MsgBox("Forward the current eMail or Create New?" & vbCrLf & vbCrLf & "Yes = Forward" & vbCrLf & "No = Create New" & vbCrLf, vbYesNo) = vbYes
        If bolForward Then
            Set mai = Application.ActiveInspector.CurrentItem.Forward
        End If
    End If
End If

If mai Is Nothing Then 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>" & _
                "<br><br>" & _
                .HTMLBody
'    .HTMLBody = "<p style='font-family:calibri'>" & strText & "</p>"
    .Display
End With

Open in new window


This time around I have shown retention of the existing body and prefixed it with your HTML

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCommented:
Just a side note:
For filling you reason comboboxes:
Private Sub userform_activate()

Dim i As Integer, x As Integer
Dim cb, cbItem, cbItems As String

cbItems = "Apple Egg Bread Cheese Milk"
cbItem = Split(cbItems)

    For i = 1 To 5
        Set cb = Controls("cboReason" & i)
        For x = LBound(cbItem) To UBound(cbItem)            
            cb.AddItem cbItem(x)        
        Next x
    Next
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Completely disagree, the code at  39472565 fully meets everything requested and should be accepted as a valid answer in the abscence of any communication from Geekamo.

This can also be inferred since there were a number of communications on that same day and all stopped with that specific post therefore given the post is compliant to the request and no clarification was requested I find it difficult to accept a determination that there is not enough information.

Chris
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
As best as I can tell,  Chris  has answered the question.

FWIW: I found this post helpful. It would be a shame to delete this knowledge.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.