New topics: Your Pet, IOU, Baby IQ, The Poisons, Birther II, Games, Future Power

Welcome to the Tech Space!

Webmaster Issues

Skip to end of metadata
Go to start of metadata

VBA programming is a great way to automate tasks in Outlook. Visual Basic For Applications programming in Office 2010 programs can do almost every operation that you can perform with a mouse, keyboard, or a dialog box.

Getting started

  • Launch VBA editor from Outlook with the key ALT + F11.

Example of Moving Emails Between Folders Using VBA

note:The MAPIFolder object in earlier versions of Outlook has been replaced by the Folder object. The Folder object inherits all the members of the MAPIFolder object. However, note that the AddToFavorites method is now hidden.

Sub MovingItems()
' Declare all the variables
    ' Using new means we don't have to use SET to set this variable
    Dim OutlookApp As New Outlook.Application
    Dim MailNS As Outlook.NameSpace
    Dim Inbox As Outlook.MAPIFolder
    Dim fldDest As Outlook.MAPIFolder
    Dim Items As Outlook.Items
    Dim Item As Object
    Dim strSearch As String

' Always required for outlook access
' Object variables must be assigned with set
    Set MailNS = OutlookApp.GetNamespace("MAPI")

    Set Inbox = MailNS.GetDefaultFolder(olFolderInbox)
    Set Items = Inbox.Items

' Display the PickFolder dialog box
    Set fldDest = MailNS.PickFolder
    strSearch = InputBox("Search in Subject")
    For Each Item In Items

        ' Do a case insensitive search. Could also use Item.Body
        If InStr(UCase(Item.Subject), UCase(strSearch)) Then
            Item.Move fldDest
        End If
    Next
    Set MailNS = Nothing
    Set Inbox = Nothing
    Set Items = Nothing
    Set fldDest = Nothing
End Sub

Save Outlook Attachments Using Visual Basic For Applications

Option Explicit

Sub SaveAtts()
    Dim ns As NameSpace
    Dim fld2SaveAtt As MAPIFolder
    Dim MailItem As Object
    Dim Att As Attachment
    Dim FileName As String
    Dim intFiles As Integer
    
    On Error GoTo HandleError
    
    Set ns = GetNamespace("MAPI")
    Set fld2SaveAtt = ns.GetDefaultFolder(olFolderInbox)
    intFiles = 0
    If fld2SaveAtt.Items.Count = 0 Then
        MsgBox "There were no messages found in your Inbox."
        Exit Sub 'there are no messages, so Exit the Sub
    End If
    'Loop through Mail Items
    For Each MailItem In fld2SaveAtt.Items
        'Loop through any attachments
        For Each Att In MailItem.Attachments
            FileName = "C:Attachments" & Trim(Att.FileName)
            Att.SaveAsFile FileName
            intFiles = intFiles + 1
         Next
    Next
' Show summary message
    If intFiles > 0 Then
        MsgBox intFiles & " attachments were saved to " ^ _
        "C:Attachments."
    Else
        MsgBox "No attachments were found"
    End If
    
    Set Att = Nothing
    Set MailItem = Nothing
    Set ns = Nothing
    Exit Sub
HandleError:
    MsgBox "Error: " & Err.Number & vbCrLf & _
            "Description: " & Err.Description & vbCrLf & _
            "The file's name is " & FileName
            intFiles = intFiles - 1
    Resume Next 'Continue saving attachments
End Sub

How to send an email with an automatic signature

This routine sends email with automatic signature, but it requires you to setup a signature first.

Sub Mail_with_Signature()

    Dim olMailItem As MailItem
    Dim ns As NameSpace
    Dim olRecips As Recipient
    Dim tmpRecips As String
    
    
    Set ns = Application.Session
    If Not ns Is Nothing Then
        ns.Logon , , False, False
    End If
    Set olMailItem = Application.CreateItem(olMailItem)
'    olMailItem.Body = "Body of Test Email"
    tmpRecips = InputBox("Enter the recipients separated by ;")
    Set myRecips = myMailItem.Recipients.Add(tmpRecips)
    olRecips.Type = olTo
    tmpRecips = InputBox("Enter the CC recipients separated by ;")
    If InStr(tmpRecips, "@") Then
        Set olRecips = myMailItem.Recipients.Add(tmpRecips)
        olRecips.Type = olCC
    End If
    tmpRecips = InputBox("Enter the BCC recipients separated by ;")
    If InStr(tmpRecips, "@") Then
        Set olRecips = olMailItem.Recipients.Add(tmpRecips)
        olRecips.Type = olBCC
    End If
    Set olRecips = Nothing
    olMailItem.Subject = "Subject of Test Email"
    If Len(Dir("c:\\TestFile.txt")) Then
        olMailItem.Attachments.Add "c:\\TestFile.txt"
    End If
    olMailItem.Display
    olMailItem.Body = "Body of Test Email" & myMailItem.Body
    olMailItem.Send
End Sub 


VBA Resources

  • Getting started with VBA in Outlook - link
Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.