wood.pecker
12-01-2011, 07:04 AM
Hello!
Iīm completely stuck in finding a properly working solution for my problem, so thatīs why I would like to ask you to help me a little bit.
First of all, this is my situation:
In my office we have an exchange 2003 server and a couple of win7-pro-clients with Outlook 2010.
We do not use personalized email-adresses - instead, every client has a mailbox such as e.g. "user1@mycompany.com".
All clients have access to a so called "public folder" in exchange to view and read incoming emails.
When we answer to an email, we use the "send as" option of exchange to send emails to our clients via these adresses (i replaced the actual ones with examples):
info@mycompany.com, support@mycompany.com, secretariat@mycompany.com and reply@mycompany.com.
For this reason, before clicking the "send" button in an email-object, we have to select the specific (and right) email-account in the "From"-field.
There, as standard, the respective Mailbox-Adress is defined (e.g. user1@mycompany.com).
If a user forgets to change this the mail gets sent via the defined Mailbox-Adress, e.g. "user1@mycompany.com" - which results in the mail not getting sent out to the customer.
(It does get bounced back of course, but this as well as "sending as"-permissions and so on shouldnīt be of further interest here)
So this is why I started looking for a VBA script solution to force the user to change the value of the "From"-field.
I found a lot on the web and then started to customize the respective vba codes for my needs with rather less than more success.
To give you an overview of what I have tried, allow me to start with the following:
First I tried to force a dialogue about the value of the "From"-field as soon as the "send" button was clicked, by using the following code.
The advantages of this code:
-user could no longer forget to change the "From"-field value, instead had to make a decision.
The disadvantages of this:
-dialogue was forced even if a selection had already been made,
-if no selection had been made AND the user didnīt want to send via "info@company.com", the user had to fill in the proper adress manually = bad usability.
So here the code:
-------------------------------------------------------------------------
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error GoTo Err001
Dim objItem As Outlook.MailItem
If Item.Class = olMail Then
If MsgBox("Send Email via info@mycompany.com?", vbYesNo) = vbYes Then
Set objItem = Item.Copy
objItem.SentOnBehalfOfName = "info@mycompany.com"
objItem.Send
Cancel = True
Else
Answer = Inputbox("Via which Email-Account do you wish to send the Email?")
Set objItem = Item.Copy
objItem.SentOnBehalfOfName = Answer
objItem.Send
Cancel = True
End If
End If
Exit Sub
Err001:
MsgBox "An error occurred!"
End Sub
-------------------------------------------------------------------------
So this was not really what I wanted and started googling for similar solutions respectively ideas.
I then thought that creating a customform could help me out.
So i created a customform with a combobox (showing a dropdown list of the Email-Accounts) and
two commandbuttons (1.: "chose this Email-Account and send", 2.: "chose this Email-Account and continue editing this mail")
I then inserted the following two codes:
...the first one under "ThisOutlookSession" in the Visual Basic Editor of Outlook.
-------------------------------------------------------------------------
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error GoTo Err001
Dim objItem As Outlook.MailItem
If Item.Class = olMail Then
UserForm1.Show
Exit Sub
Err001:
MsgBox "An error occurred"
End Sub
-------------------------------------------------------------------------
...and the second one in the userform itself:
-------------------------------------------------------------------------
Private Sub userform_initialize()
With UserForm1.ComboBox1
.AddItem "info@mycompany.com"
.AddItem "secretariat@mycompany.com"
.AddItem "support@mycompany.com"
.AddItem "reply@mycompany.com"
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = 1
MsgBox "Please leave this dialogue via the buttons!", vbOKOnly + vbInformation, "Make a selection and click a button."
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
-------------------------------------------------------------------------
My problems now are quite obvious when you look at the yet incomplete code above:
1. How can I reach that a click on CommandButton1 "takes" the selection made in the Combobox1 (only if a selection was made!) and "transports" it into the "From"-field, then triggers the send-action and then closes the dialogue?
2. How can I reach that a click on CommandButton2 "takes" the selection made in the Combobox1 (only if a selection was made!) and "transports" it into the "From"-field, then closes the dialogue and lets the user continue editing the mail?
Finally, as my head was smoldering like a chimney, I started wondering if I had made it all more complicated then it had to be.
Because what I actually wanted could be realized by a script which sounds easy to realize, but seems a giordian knot for me.
Therefore this could then be my last question:
3. What code should I use if I just needed a vba script that does the following:
-When the "send" button gets clicked:
-check if the value of the "From"-field equals (e.g.!) user1@mycompany.com (which would be the standard),
-if yes: show msgbox that says: "select the right Email-Account!", close the msgbox on confirmation oft this message and lead the user back to editing the mail (respectively correct th "From"-field)
-if no: (which means: it equals any of the Email-Accounts above) trigger the send-action and close the msgbox.
Ok.
Thatīs it :-)
I really really hope that someone with a little more insight into vba programming can help me out as I am somehow stuck at this point and donīt know how to move forward.
Thanks in advance!
Iīm completely stuck in finding a properly working solution for my problem, so thatīs why I would like to ask you to help me a little bit.
First of all, this is my situation:
In my office we have an exchange 2003 server and a couple of win7-pro-clients with Outlook 2010.
We do not use personalized email-adresses - instead, every client has a mailbox such as e.g. "user1@mycompany.com".
All clients have access to a so called "public folder" in exchange to view and read incoming emails.
When we answer to an email, we use the "send as" option of exchange to send emails to our clients via these adresses (i replaced the actual ones with examples):
info@mycompany.com, support@mycompany.com, secretariat@mycompany.com and reply@mycompany.com.
For this reason, before clicking the "send" button in an email-object, we have to select the specific (and right) email-account in the "From"-field.
There, as standard, the respective Mailbox-Adress is defined (e.g. user1@mycompany.com).
If a user forgets to change this the mail gets sent via the defined Mailbox-Adress, e.g. "user1@mycompany.com" - which results in the mail not getting sent out to the customer.
(It does get bounced back of course, but this as well as "sending as"-permissions and so on shouldnīt be of further interest here)
So this is why I started looking for a VBA script solution to force the user to change the value of the "From"-field.
I found a lot on the web and then started to customize the respective vba codes for my needs with rather less than more success.
To give you an overview of what I have tried, allow me to start with the following:
First I tried to force a dialogue about the value of the "From"-field as soon as the "send" button was clicked, by using the following code.
The advantages of this code:
-user could no longer forget to change the "From"-field value, instead had to make a decision.
The disadvantages of this:
-dialogue was forced even if a selection had already been made,
-if no selection had been made AND the user didnīt want to send via "info@company.com", the user had to fill in the proper adress manually = bad usability.
So here the code:
-------------------------------------------------------------------------
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error GoTo Err001
Dim objItem As Outlook.MailItem
If Item.Class = olMail Then
If MsgBox("Send Email via info@mycompany.com?", vbYesNo) = vbYes Then
Set objItem = Item.Copy
objItem.SentOnBehalfOfName = "info@mycompany.com"
objItem.Send
Cancel = True
Else
Answer = Inputbox("Via which Email-Account do you wish to send the Email?")
Set objItem = Item.Copy
objItem.SentOnBehalfOfName = Answer
objItem.Send
Cancel = True
End If
End If
Exit Sub
Err001:
MsgBox "An error occurred!"
End Sub
-------------------------------------------------------------------------
So this was not really what I wanted and started googling for similar solutions respectively ideas.
I then thought that creating a customform could help me out.
So i created a customform with a combobox (showing a dropdown list of the Email-Accounts) and
two commandbuttons (1.: "chose this Email-Account and send", 2.: "chose this Email-Account and continue editing this mail")
I then inserted the following two codes:
...the first one under "ThisOutlookSession" in the Visual Basic Editor of Outlook.
-------------------------------------------------------------------------
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error GoTo Err001
Dim objItem As Outlook.MailItem
If Item.Class = olMail Then
UserForm1.Show
Exit Sub
Err001:
MsgBox "An error occurred"
End Sub
-------------------------------------------------------------------------
...and the second one in the userform itself:
-------------------------------------------------------------------------
Private Sub userform_initialize()
With UserForm1.ComboBox1
.AddItem "info@mycompany.com"
.AddItem "secretariat@mycompany.com"
.AddItem "support@mycompany.com"
.AddItem "reply@mycompany.com"
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = 1
MsgBox "Please leave this dialogue via the buttons!", vbOKOnly + vbInformation, "Make a selection and click a button."
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
-------------------------------------------------------------------------
My problems now are quite obvious when you look at the yet incomplete code above:
1. How can I reach that a click on CommandButton1 "takes" the selection made in the Combobox1 (only if a selection was made!) and "transports" it into the "From"-field, then triggers the send-action and then closes the dialogue?
2. How can I reach that a click on CommandButton2 "takes" the selection made in the Combobox1 (only if a selection was made!) and "transports" it into the "From"-field, then closes the dialogue and lets the user continue editing the mail?
Finally, as my head was smoldering like a chimney, I started wondering if I had made it all more complicated then it had to be.
Because what I actually wanted could be realized by a script which sounds easy to realize, but seems a giordian knot for me.
Therefore this could then be my last question:
3. What code should I use if I just needed a vba script that does the following:
-When the "send" button gets clicked:
-check if the value of the "From"-field equals (e.g.!) user1@mycompany.com (which would be the standard),
-if yes: show msgbox that says: "select the right Email-Account!", close the msgbox on confirmation oft this message and lead the user back to editing the mail (respectively correct th "From"-field)
-if no: (which means: it equals any of the Email-Accounts above) trigger the send-action and close the msgbox.
Ok.
Thatīs it :-)
I really really hope that someone with a little more insight into vba programming can help me out as I am somehow stuck at this point and donīt know how to move forward.
Thanks in advance!