asked on

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.

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

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...

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

Chris Bottomley

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

Geekamo

ASKER

@ 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

Chris Bottomley

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

Geekamo

ASKER

@ 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

Chris Bottomley

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

Geekamo

ASKER

@ 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

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve

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

Martin Liss

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

Chris Bottomley

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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.