How the macro works

Because this code will be run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools  ⇒  References. Scroll down until you find the entry Microsoft Outlook xx Object Library, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object

'Step 2: Open Outlook and start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

'Step 3: Build your mail item and send
    With OLMail
    .To = "     [email protected] ; [email protected] "
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Sample File Attached"
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With

'Step 4: Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.

Step 2 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. Step 2 also creates a mail item, similar to manually selecting the New Message button in Outlook.

Step 3 builds the profile of your mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. Note that the recipients are entered in quotes and separates recipients with a semicolon. The standard syntax for an attachment is .Attachments.Add “File Path”. In this code, you specify the current workbook’s file path with the syntax ActiveWorkbook.Fullname, effectively setting the current workbook as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing.

Releasing the objects assigned to your variables is generally good practice to reduce the chance of any problems caused by rouge objects that may remain open in memory. As you can see in Step 4, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object

'Step 2: Copy range, paste to new workbook, and save it
    Sheets("Revenue Table").Range("A1:E7").Copy
    Workbooks.Add
    Range("A1").PasteSpecial xlPasteValues
    Range("A1").PasteSpecial xlPasteFormats
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "TempRangeForEmail.xlsx"

'Step 3: Open Outlook and start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

'Step 4: Build your mail item and send
    With OLMail
    .To = " [email protected] ; [email protected] "
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Sample File Attached"
    .Attachments.Add (ThisWorkbook.Path & "TempRangeForEmail.xlsx")
    .Display
    End With

'Step 5: Delete the temporary Excel file
    ActiveWorkbook.Close SaveChanges:=True
    Kill ThisWorkbook.Path & "TempRangeForEmail.xlsx"

'Step 6: Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.

Step 2 copies a specified range and pastes the values and formats to a temporary Excel file. The macro then saves the temporary file, giving it a file path and filename.

Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.

Step 4 builds the profile of the mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. Note that the recipients are entered with quotes and separated with a semicolon.

You specify your newly created temporary Excel file path as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing it.

You don’t want to leave temporary files hanging out there, so after the email has been sent, Step 5 deletes the temporary Excel file you created.

It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 6, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools  ⇒  References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object

'Step 2: Copy worksheet, paste to new workbook, and save it
    Sheets("Revenue Table").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "TempRangeForEmail.xlsx"

'Step 3: Open Outlook and start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

'Step 4: Build your mail item and send
    With OLMail
    .To = "     [email protected] ; [email protected] "
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Sample File Attached"
    .Attachments.Add (ThisWorkbook.Path & "TempRangeForEmail.xlsx")
    .Display
    End With

'Step 5: Delete the temporary Excel file
    ActiveWorkbook.Close SaveChanges:=True
    Kill ThisWorkbook.Path & "TempRangeForEmail.xlsx"

'Step 6: Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

Step 1 first declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.

Step 2 copies a specified worksheet and pastes the values and formats to a temporary Excel file. You then save the temporary file, giving it a file path and filename.

Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. You also create a mail item. Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.

Step 4 builds the profile of the mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. The recipients are entered in quotes and separated by a semicolon.

In this code, you specify your newly created temporary Excel file path as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing it.

You don’t want to leave temporary files hanging out there, so after the email has been sent, you delete the temporary Excel file you created.

It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. As you can see in the code, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object

'Step 2: Open Outlook and start a new mail item
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

'Step 3: Build your mail item and send
    With OLMail
    .To = " [email protected] ; [email protected] "
    .CC = ""
    .BCC = ""
    .Subject = "Monthly Report Email with Link"
    .HTMLBody = _
    "<p>Monthly report is ready. Click to Link to get it.</p>" &
    "<p><a href=" & Chr(34) & "Z:DownloadsMonthlyReport.xlsx" & _
    Chr(34) & ">Download Now</a></p>"     .Display
    End With

'Step 4: Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.

Step 2 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. This step also creates a mail item. Step 2 also creates a mail item, similar to manually selecting the New Message button in Outlook.

Step 3 builds the profile of your mail item, including the To recipients, CC recipients, BCC recipients, subject, and HTMLBody.

To create the hyperlink, you need to use the HTMLBody property to pass HTML tags. You can replace the file path address shown in the macro with the address for your file. Note that this macro uses the .Display method, which opens the email for your review. You can replace .Display with .Send to automatically fire off the email without reviewing it.

It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 4, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools  ⇒  References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    Dim MyCell As Range
    Dim MyContacts As Range

'Step 2: Define the range to loop through
    Set MyContacts = Sheets("Contact List").Range("H2:H21")

'Step 3: Open Outlook
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon

'Step 4: Add each address in the contact list
    With OLMail

          For Each MyCell In MyContacts
          .BCC = .BCC & Chr(59) & MyCell.Value
          Next MyCell

        .Subject = "Sample File Attached"
        .Body = "Sample file is attached" .Attachments.Add ActiveWorkbook.FullName
        .Display

    End With

'Step 5: Memory cleanup
    Set OLMail = Nothing
    Set OLApp = Nothing

End Sub

Step 1 declares four Object variables: OLApp exposes the Outlook Application object, OLMail holds a mail item, MyCell holds an Excel range, and MyContacts holds an Excel range.

Step 2 points the MyContacts variable to the range of cells that contain your email addresses. You'll be looping through this range of cells to add email addresses to your email.

Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. You also create a mail item. Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.

Step 4 builds the profile of your mail item. Note that you are looping through each cell in the MyContacts range and adding the contents (which are email addresses) to BCC. You use the BCC property instead of To or CC so that each recipient gets an email that looks as though it was sent only to him or her. Your recipients won't be able to see the other email addresses because they are sent with BCC (Blind Courtesy Copy).

This macro uses the .Display method, which opens the email for your review. You can replace .Display with .Send to automatically fire off the email without reviewing.

It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 5, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim ns As Namespace
    Dim MyInbox As MAPIFolder
    Dim MItem As MailItem
    Dim Atmt As Attachment
    Dim FileName As String

'Step 2: Set a reference to your inbox
    Set ns = GetNamespace("MAPI")
    Set MyInbox = ns.GetDefaultFolder(olFolderInbox)

'Step 3: Check for messages in your inbox; exit if none
    If MyInbox.Items.Count = 0 Then
    MsgBox "No messages in folder."
    Exit Sub
    End If

'Step 4: Create directory to hold attachments
    On Error Resume Next
    MkDir "C:TempMyAttachments"

'Step 5: Start to loop through each mail item
    For Each MItem In MyInbox.Items

'Step 6: Save each attachment and then go to the next attachment
    For Each Atmt In MItem.Attachments
    FileName = "C:TempMyAttachments" & Atmt.FileName
    Atmt.SaveAsFile FileName
    Next Atmt

'Step 7: Move to the next mail item
    Next MItem

'Step 8: Memory cleanup
    Set ns = Nothing
    Set MyInbox = Nothing

End Sub

Step 1 declares five variables. ns is an object that exposes the MAPI namespace. MyInbox exposes the target mail folder. MItem exposes the properties of a mail item. Atmt is an Object variable that holds an Attachment object. FileName is a String variable that holds the name of the attachment.

Step 2 sets the MyInbox variable to point to the inbox for the default mail client.

Step 3 performs a quick check to make sure that the inbox contains messages. If there are no messages, the macro exits the procedure and displays a message box stating that there are no messages.

Step 4 creates a directory to hold the attachments you find. Although you could use an existing directory, creating a directory specifically for the attachments you save is usually best. Here, you create that directory on the fly. You use On Error Resume Next to ensure that the code does not error out if the directory you're trying to create already exists.

Step 5 starts the loop through each mail item in the target mail folder.

Step 6 ensures that each mail item you loop through is checked for attachments. As you loop, you save each attachment in the specified directory you created.

Step 7 loops back to Step 5 until there are no more mail items to go through.

Releasing the objects assigned to your variables is good general practice because it reduces the chance of any problems caused by rogue objects that may remain open in memory. Step 8 simply sets the variable to Nothing.

How to use it

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.

How the macro works

Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools  ⇒  References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.

Sub Macro1()

'Step 1: Declare your variables
    Dim ns As Namespace
    Dim MyInbox As MAPIFolder
    Dim MItem As Object
    Dim Atmt As Attachment
    Dim FileName As String
    Dim i As Integer

'Step 2: Set a reference to your inbox
    Set ns = GetNamespace("MAPI")
    Set MyInbox = ns.GetDefaultFolder(olFolderInbox)

'Step 3: Check for messages in your inbox; exit if none
    If MyInbox.Items.Count = 0 Then
    MsgBox "No messages in folder."
    Exit Sub
    End If

'Step 4: Create directory to hold attachments
    On Error Resume Next
    MkDir "C:OffTheGridMyAttachments"

'Step 5: Start to loop through each mail item
    For Each MItem In MyInbox.Items

'Step 6: Check for the words Data Submission in Subject line
    If InStr(1, MItem.Subject, "Data Submission") < 1 Then
    GoTo SkipIt
    End If

'Step 7: Save each attachment with a log number
    i = 0
    For Each Atmt In MItem.Attachments
    FileName = _
    "C:TempMyAttachmentsAttachment-" & i & "-" & Atmt.FileName
    Atmt.SaveAsFile FileName
    i = i + 1
    Next Atmt

'Step 8: Move to the next mail item
SkipIt:
    Next MItem

'Step 9: Memory cleanup
    Set ns = Nothing
    Set MyInbox = Nothing

End Sub

Step 1 first declares six variables. ns is an object that exposse the MAPI namespace. MyInbox exposes the target mail folder. MItem exposes the properties of a mail item. Atmt is an Object variable that holds an Attachment object. FileName is a String variable that holds the name of the attachment. i is an Integer variable used to ensure that each attachment is saved as a unique name.

Step 2 sets the MyInbox variable to point to the inbox for your default mail client.

Step 3 performs a quick check to make sure that your inbox contains messages. If there are no messages, it exits the procedure and displays a message box stating that there are no messages.

Step 4 creates a directory to hold the attachments you find. Note that it uses On Error Resume Next to ensure that the code does not error out if the directory you're trying to create already exists.

Step 5 starts the loop through each mail item in the target mail folder.

In Step 6, you use the Instr function to check whether the string “Data Submission” is in the subject line of the email. If that string does not exist, you don't care about any attachments to that message. Therefore, you force the code to go to the SkipIt reference (in Step 8). The line of code immediately following the SkipIt reference is essentially a Move Next command, telling the procedure to move to the next mail item.

Step 7 loops through and saves each attachment in the specified directory you created. You add a running integer to the name of each attachment to ensure that each one is saved as a unique name, to avoid overwriting attachments.

Step 8 loops back to Step 5 until there are no more mail items to go through.

Releasing the objects assigned to your variables is generally good practice because it reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 9, you simply set the variable to Nothing.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code into the newly created module.