Geekamo
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
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
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
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
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
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
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
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
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
Chris
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a side note:
For filling you reason comboboxes:
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
I've requested that this question be deleted for the following reason:
Not enough information to confirm an answer.
Not enough information to confirm an answer.
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
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
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.
FWIW: I found this post helpful. It would be a shame to delete this knowledge.
Open in new window
The line mai.display can easily be replaced by your assignment text and then sent instead of displayed.
Chris