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
- VBA Code Samples - Also covers VBA for things other than outlook
- VBA programming tutorials - link
- How to write text files from VBA - http://zo-d.com/blog/archives/programming/vba-writing-to-a-text-file-ms-project-excel.html
Labels:
None
Add Comment