Help send BCC from excel using VBA

Posted on 2009-04-15
Last Modified: 2013-12-17
I can send a workbook to multiple recipients using the following
Workbooks("Sendemail.xls").SendMail Recipients:="";""

How do I add other addresses as BCC
Question by:scarab66
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello scarab66,

    You cannot send as a BCC using SendMail, but you can automate Outlook...

    Dim olApp As Object
    Dim olMsg As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0)
    With olMsg
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "subject"
        .Body = "body"
        .Attachments.Add Workbooks("Sendemail.xls").Path & "\Sendemail.xls"
    End With

    Set olMsg = Nothing
    Set olApp = Nothing

    Using that method may trigger an Outlook security measure implemened starting in Outlook 2000 SP2, and persisting
    at least through 2002 and 2003.

    To disable it, I've used three products:

    1) Express ClickYes
    There is a free version and a Pro version for ~$30 with more features.  I've used the pro version.
    It works as advertised.  I'm not using it currently.  I'm using the MAPILab product instead.

    2) MAPILab Advanced Outlook Security
    A free COM add-in for Outlook.  I am using it now and I like it

    3) Redemption
    A DLL you can use in your code to disable the warnings.  Free download, but you must pay a
    license to distribute.  I've used it and like it.



    Author Comment

    G'day Patrick
    Can I use Outlook Express or even better Thunderbird

    Author Comment

    What is wrong with the followind
    .Attachments.Add Workbooks("Sendemail.xls").Path & "E:\AA Daily Calcs\Test\Sendemail.xls"

    Open in new window

    LVL 92

    Accepted Solution

    .Attachments.Add "E:\AA Daily Calcs\Test\Sendemail.xls"

    Author Closing Comment

    Thanks Patrick

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now