Tag Archives: email

Send email from Excel using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.67 out of 5)

Tired of writing tons of similar emails to your recipients? Preparing a personalized newsletter in Excel? Sending an email from Excel is a useful feature in case you need email notifications or you are building a mailing list in Excel. Below find a quickly drafted method for sending emails from your Outlook application. The prerequisite is to have Outlook Exchange installed and configured to some default email account. The procedure will then leverage this account to send emails. Similarly this can be use to send emails from VBscript – just remove any variable data type declarations and you should be good to go (e.g. “Dim outApp” instead of “Dim outApp as Object”).

Send email from Excel

The code below will utilize your Outlook application to created an Mail item, and send the email.

Sub SendEmailFromOutlook(body As String, subject As String, toEmails As String, ccEmails As String, bccEmails As String)
    Dim outApp As Object
    Dim outMail As Object
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)

    With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Send 'Send the email
    End With

    Set outMail = Nothing
    Set outApp = Nothing
End Sub

Tweak: Save email in Outlook from Excel

What if you do not want to send the email, but instead just send a draft to finish or send later? Replace Send with Save in the above code snippet as show below:

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Save 'Instead of send
    End With

Tweak: Display draft instead of sending an email

An additional modification of the procedure could be to simply display the email draft instead of sending/saving it. Replace Send with Display in the above code snippet as show below. You will be presented with the draft email message which you can tinker with and send by yourself.

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Display 'Instead of send/save
    End With

Adding attachments to email from Excel

Sometimes text/html is not the only content you want to share with your recipients. What about adding some attachments to the email? Just add the code snippet below to the original code snippet above:

Dim filePath as String
filePath = ("C:\someFile.txt")
outMail.Attachments.Add filePath 'Send some text file

'Attach the current Workbook
outMail.Attachments.Add ActiveWorkbook.FullName


Sending emails from Excel can save you a lot of time. This is a very useful example of Excel automation. I commonly see sending emails from Excel being used in these scenarios:

  • Managing email distribution lists from Excel
  • Sending notifications emails from Excel
  • Creating draft emails

Additional tip: send Excel as PDF

As most of you know Excel is great for preparing reports. Charts, numbers etc. You can of course create a text message with this data. However sometimes that is simply not as convenient as sending an image/pdf and distributing these PDF reports to recipients. Let’s extend our snippet further to send a pdf Excel report with just the output instead of your entire workbook.

Dim ws as Worksheet
Set ws = Activesheet
ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _

More examples

Above I tried to focus on the most common examples of sending emails via Excel. There are definitely more scenarios. I think Ron de Bruin did a great job of listing most of them. Check his website here.

Lync Spy – monitor and get notified of anyones status changes

1 Star2 Stars3 Stars4 Stars5 Stars (26 votes, average: 3.85 out of 5)

Lately I was having issues with getting in contact with some of my colleagues. The problem was that I could not contact them by phone and Lync (now Skype for Business) / Email were my only 2 options. Sending emails is not a quick way of contacting anyone and unfortunately you can’t send a Lync message to people who are offline so they get it as soon as they log in or are available. Also, I didn’t want to stalk my laptop all the time just waiting for an availability notification from someone I needed to connect to. Desperate and frustrated, I mashed up a simple app for tracking Lync statuses of any Lync contacts – a Lync Spy (aka Lync Tracker) application.

The Lync Sky works equally for Skype for Business – this is only a branding change on Microsoft’s end

What does the Lync Spy do?

Basically it connects with Lync to track any changes in status of any number of your Lync contacts. In detail it:

  • Tracks any changes in users Lync statuses
  • Sends email notification of any changes to a Lync status
  • Logs user status changes to a CSV file (in case you want to spy on changes in their availability)
Lync Spy / Lync Tracker
Lync Spy / Lync Tracker

How to use it to track Lync/Skype status changes?

Add users to track

Add any number of users to the watch list by providing their Lync / Skype for Business email and clicking the Add to list button.

Similarly you can remove users by selecting them and clicking Remove selected.

Set tracked statuses

Check the status you want to watch out for e.g. Available, Away etc. Whenever anyone from your watch list will change status to any of these statuses you will be notified and/or this will be logged

Set email notifications or CSV log

Check whether you want to receive an email notification in case anyone changes their status to the statuses which you selected.
Check whether you want to log all change status events to a CSV file

Turn it On!

All you need to do now is change the On / Off option to On.

That’s it! Just remember not to turn Lync on and to keep your Outlook Exchange application open if you want to send/receive notification of status changes!

Feel free to download the app here (requires .NET 4):

The app is licensed under a standard GNU Public Licence

Get Outlook recipients information via VBA (Outlook users data)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.00 out of 5)

Being a corporate Consultant I always thought of the vast amount of data stored in various corporate databases that could be put to good use. Lot of data is stored and is accessible via Outlook – although mostly not appreciated.

I once received an assignment which required analyzing the data of all employees in our company. One way to approach this assignment was to contact HR, which would then need to contact regional HR … and so on. A process that would last at least a week before I could get my hands on the actual data. And even then this would require verifying the data and some cleansing. The other easier way – the corporate Outlook correspondence group containing all active employees and to somehow dump this data to an Excel/CSV file for analysis. Hence, I conducted a short research resulting in the following VBA function:

VBA to extract Outlook recipient information

Here is the VBA code:

Sub DumpOutlookData()
    Dim NewMail As Outlook.MailItem
    Set NewMail = Application.ActiveInspector.CurrentItem
    Dim s As String, filePath As String
    s = ""
    On Error Resume Next
    For Each Recipient In NewMail.Recipients
      s = s & Recipient.AddressEntry.GetExchangeUser.Alias
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Name
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.JobTitle
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.City
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Department & vbNewLine
    Next Recipient
    Set FSO = CreateObject("Scripting.FileSystemObject")
    filePath = Date & "-Outlook Data.txt"
    Set oFile = FSO.CreateTextFile(filePath)
    oFile.WriteLine s
    Call MsgBox("Saved to: " & filePath)
End Sub

Notice that what it does is locate the current open email and inspect its recipients. Next it traverses the whole list of recipients and dumps that data to your default VBA directory.

How to dump Outlook recipient information – Step by step

To make sure you are using the code above correctly be sure to follow the instructions below:

Insert the function to your Outlook Visual Basic project

Go to Developer ribbon tab and select Visual Basic. In the VBE (Visual Basic Editor) right click on your VBA Project and select New Module. Insert the macro above.

Add recipients to Outlook draft email

Open a new email message in Outlook and add the desired recipients to the To: section e.g. if adding a correspondence group be sure to expand the list to list all recipients individually. This is an important step – be sure to list INDIVIDUAL recipients as the macro will not expand any group emails

Execute the macro

Go to Developer ribbon tab, select Macros and run the DumpOutlookData procedure. Wait for it to dump the data to the txt file (to your default folder – Documents, read more how to change default directory.). This might take some time depending on how many email recipients you want to dump to your file.
outlook recipients dump
You can also extend the macro to get additional information on Outlook recipients like:

  • Manager (Recipient.AddressEntry.Manager.Alias)
  • Office Location (Recipient.AddressEntry.GetExchangeUser.OfficeLocation)
  • Phone Numbers (Recipient.AddressEntry.GetExchangeUser.MobileTelephoneNumber )

..and other information.

Where to go from here?

Ok so you got the data in a semicolon ; separated CSV file. What next?

Cool ideas to utilize this data

There are tons of useful things you can do with this Outlook recipient information. Here are just some:

Hopefully you see what an incredible resource everyone in a company has – which you can utilize for tons of internal company projects without needing to approach HR.