We help IT Professionals succeed at work.
Private
Troubleshooting Question

Help in VBA to set BCC email dependent on sender in Outlook 2010

jana
jana asked
on
79 Views
Last Modified: 2020-09-20
Hi Experts!

Please look at the VBA below.  The script, when creating an email, would always set a fixed BCC email address.  I would like to modify the VBA so it would place the email based on the sender chosen:


The image above display the result of the VBA,  What I am trying to accomplish is if I change the "From" or sender", I want the BCC to be that emails.

This is currently the VBA script:
Private Sub m_Inspectors_NewInspector(ByVal Inspector As Inspector)
'Set BCC emails
'This routine is activated when a NEW EMAIL is being created
 Dim objRecip As Recipient
 Dim strMsg As String
 Dim strBCC As String

    If TypeName(Inspector.currentItem) = "MailItem" Then
        If Inspector.currentItem.entryid = "" Then
        ' #### USER OPTIONS ####
        ' address for Bcc -- must be SMTP address or resolvable
        ' to a name in the address book
            strBCC = "jana@domain.com"
            Set objRecip = Inspector.currentItem.Recipients.Add(strBCC)
            objRecip.Type = olBCC
            If Not objRecip.Resolve Then
                strMsg = "Could not resolve the Bcc recipient."
                MsgBox strMsg, vbInformation, "Could Not Resolve Bcc Recipient"
            End If
            Set objRecip = Nothing
        End If
    End If
End Sub
Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
To my knowledge, there's no event for clicking From and changing it. You may have to resort to using the ItemSend event instead. You won't really see the BCC being added (probably displayed for mere milliseconds), but it will definitely be filled in.

Author

Commented:
Understood in the event, but is there a way in that routine to read or detect who is the sender? maybe extract from there?

Or maybe going thru the Draft folder? When a email is started to work with, I have noticed that it gets created in Draft folder of Outlook; maybe detect a unique number in the open draft email and then read thru the Draft folder? (sorry just think outload there)
KimputerIT Manager
CERTIFIED EXPERT

Commented:
The From header is detected in the ItemSend event as 

Mailitem.SentOnBehalfOfName

Do an if or case on that, and decide your BCC based on that.

Author

Commented:
Can u prove an example? if based on the script provided, greatly appreciated

Author

Commented:
(I meant "can u provide an example?")
KimputerIT Manager
CERTIFIED EXPERT

Commented:
Start with this first:

Option Explicit


Public WithEvents myOlApp As Application


Private Sub myOlApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
 
 Dim prompt As String
 
 prompt = "Are you sure you want to send from: " & Item.SentOnBehalfOfName
 
 If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo Then
 
 Cancel = True
 
 End If
 
End Sub


Private Sub Application_Startup()
 
 Set myOlApp = Application
 
End Sub

This is purely for DEBUGGING reasons.
Send a few emails from each account.

In the best case scenario, your own account is either clearly your own, or could be empty.
All the others NEED to have something filled in. Write down the strings, as they're not always the email address (usually the display name)

After you've collected all the variables, you can adjust the code to have a CASE on those strings.




Author

Commented:
Copied the code in ThisOutlookSession and and various emails, but nothing happened.

I exit and enter the outlook apps again and got this message:

What am I doing wrong?
KimputerIT Manager
CERTIFIED EXPERT

Commented:
You probably have other code already (same name Sub Application_Startup). If you don't need it, delete it. If you need it, merge the subs, so only one remains.

Author

Commented:
Yes, u r right, had 'Sub Application_Startup' already.

Ok, I merged your  'Sub Application_Startup'  contents with the present code.  

Placed 'Public WithEvents myOlApp As Outlook.Application'  at the top of  ThisOutlookSession - also place debug on it to see if its detected, but doesn't work.

What am i missing?
KimputerIT Manager
CERTIFIED EXPERT

Commented:
Nothing happens at all when sending emails?

Author

Commented:
Click 'Send', just sent the email, the code was not detected.

Author

Commented:
Looking at the code, I noticed that 'item' of 'Item.SentOnBehalfOfName' is not declared
How should I declare? How?
KimputerIT Manager
CERTIFIED EXPERT

Commented:
It doesn't need to be declared:

Private Sub myOlApp_ItemSend(ByVal Item As Object, Cancel As Boolean) 
Does ANY other code work at all? If not, you didn't enable macros in your Outlook Options > Trust Center yet.

Author

Commented:
None of the code work.  And macros are enable (I use series of routine on a daily bases).

To test further I deleted everything from ThisOutlookSession  and posted you code as is, no results (notice the image below, your code is the only in the section). I assume your code should be trigerred when sending an email -  when sending an email, it sends, your question doesn't pop up.

KimputerIT Manager
CERTIFIED EXPERT

Commented:
Slight code change, please try again (copy code from same post up here)

Author

Commented:
What? Sorry, don't understand/.
KimputerIT Manager
CERTIFIED EXPERT

Commented:
I updated the code. Copy and Paste again in your Outlook.

Author

Commented:
Sorry, but u haven't upload I don't see it.  Can u post please.
KimputerIT Manager
CERTIFIED EXPERT

Commented:
I also told you, I UPDATED the code, so look back up please.

Author

Commented:
I understand, but where is it?
KimputerIT Manager
CERTIFIED EXPERT

Commented:
You could've read just up here, it's only the 6th post or something? You copied it once. Just copy it again.

https://www.experts-exchange.com/questions/29190531/Help-in-VBA-to-set-BCC-email-dependent-on-sender-in-Outlook-2010.html#a43135811 


Author

Commented:
But u said u updated the code.  I didn't look at your 'entry' because I thought u entered a new entry.

u mean u went to your entry and modified it?

Author

Commented:
Ok, copied over from you entry where the code is, no results.

Author

Commented:
also just finish comparing your code in the entry and the code I had copied initially, they are the same, so same results.
KimputerIT Manager
CERTIFIED EXPERT

Commented:
Are you using a proper compare?

You can clearly see it without a compare app:

Old code:
Public WithEvents myOlApp As Outlook.Application
New code:

Public WithEvents myOlApp As Application 

Author

Commented:
Value is empty on 'Item.SentOnBehalfOfName':

I guess the best way is to show what I did with your code and where I placed it:

This is all the code in "ThisOutlookSession"

 Private WithEvents m_Inspectors As Outlook.Inspectors
 Private m_Folder As Outlook.MAPIFolder
 
'2020-0806 EE Bcc question
 Public WithEvents myOlApp As Application

Private Sub Application_Startup()
 Set m_Inspectors = Application.Inspectors
 
'2020-0806 EE Bcc question
 Set myOlApp = Application

End Sub

Private Sub m_Inspectors_NewInspector(ByVal Inspector As Inspector)
'Set BCC emails
'This routine is activated when a NEW EMAIL is being created
 Dim objRecip As Recipient
 Dim strMsg As String
 Dim strBCC As String

    If TypeName(Inspector.currentItem) = "MailItem" Then
        If Inspector.currentItem.entryid = "" Then
        ' #### USER OPTIONS ####
        ' address for Bcc -- must be SMTP address or resolvable
        ' to a name in the address book
            strBCC = "jana@domain.com"
            Set objRecip = Inspector.currentItem.Recipients.Add(strBCC)
            objRecip.Type = olBCC
            If Not objRecip.Resolve Then
                strMsg = "Could not resolve the Bcc recipient."
                MsgBox strMsg, vbInformation, "Could Not Resolve Bcc Recipient"
            End If
            Set objRecip = Nothing
        End If
    End If
End Sub

'2020-0806 EE Bcc question
Private Sub myOlApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
 Dim prompt As String
 prompt = "Are you sure you want to send from: " & Item.SentOnBehalfOfName
 If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo Then
 Cancel = True
 End If
End Sub


Where is the problem?
IT Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
So I am assuming since u did not comment on the VBA script, then it’s ok.

Will test with another account.

Author

Commented:
Please not close this question yet.  There has be lot of work! will review and port status!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.