How to Reply to Mail using Outlook VBA

Copy formatted word document to Outlook mail using VBA


Here is a simple snippet that replies to the mail using VBA. The contents of the document has earlier been saved as HTML and the HTML is copied to the mail Item

Public Sub ReplyWithHTML()
Dim oMail As Outlook.MailItem
Dim oFSO
Dim oFS
If Application.ActiveExplorer.Selection.Count Then
If TypeOf Application.ActiveExplorer.Selection(1) Is Outlook.MailItem Then
Set oMail = Application.ActiveExplorer.Selection(1).Reply

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFS = oFSO.OpenTextFile("C:\ForBlogger\formedSample.html")

stext = oFS.readall
oMail.BodyFormat = olFormatHTML
oMail.HTMLBody = stext & vbCr & oMail.HTMLBody
oMail.Display
End If
End If
End Sub

Document Variables in Outlook using VBA

Add Hidden Data using StorageItem in Outlook VBA

There are multiple ways to have a template in Outlook for achieving tasks. Sometimes, you will require to hold some document variable in outlook like you do with Microsoft Word. The following example shows a simple way to add some text in drafts folder. This will not be visible to user.

Sub Create_Hidden_Data()

Dim oNs As Outlook.NameSpace

Dim oFld As Outlook.Folder

Dim oSItem As Outlook.StorageItem

On Error GoTo OL_Error

oNs = Application.GetNamespace("MAPI")

oFld = oNs.GetDefaultFolder(olFolderDrafts)

oSItem = oFld.GetStorage("My Appt Template", olIdentifyBySubject)

oSItem.UserProperties.Add("My Footer", olText)

oSItem.UserProperties("My Footer").Value = "VBADud - Samples & Tips on VBA"

oSItem.UserProperties.Add("My Body", olText)

oSItem.UserProperties("My Body").Value = "Hi" & vbCrLf & "Requesting a appointment with you for discussing..."

oSItem.Save()

Exit Sub

OL_Error:

MsgBox(Err.Description)

Err.Clear()

End Sub

The Properties are stored in Drafts Folder and can be retrieved using the following code

Sub GetData_From_StorageItem()

Dim oNs As Outlook.NameSpace

Dim oFL As Outlook.Folder

Dim oItem As Outlook.StorageItem

On Error GoTo OL_Error

oNs = Application.GetNamespace("MAPI")

oFld = oNs.GetDefaultFolder(olFolderDrafts)

oItem = oFld.GetStorage("My Appt Template", olIdentifyBySubject)

If oItem.Size <> 0 Then

MsgBox(oItem.UserProperties("My Footer"))

MsgBox(oItem.UserProperties("My Body"))

End If

Exit Sub

OL_Error:

MsgBox(Err.Description)

Err.Clear()

End Sub

'StorageItem is a message object in MAPI that is always saved as a hidden item in the parent folder and stores private data for Outlook solutions.

'

'A StorageItem object is stored at the folder level, allowing it to roam with the account and be available online or offline.

'

'The Outlook object model does not provide any collection object for StorageItem objects. However, you can use Folder.GetTable to obtain a Table with all the hidden items in a Folder, when you specify the TableContents parameter as olHiddenItems. If keeping your data private is of a high concern, you should encrypt the data before storing it.

'

'Once you have obtained a StorageItem object, you can do the following to store solution data:

'

'Add attachments to the item for storage.

'Use explicit built-in properties of the item such as Body to store custom data.

'Add custom properties to the item using UserProperties.Add method. Note that in this case, the optional AddToFolderFields and DisplayFormat arguments of the UserProperties.Add method will be ignored.

'Use the PropertyAccessor object to get or set custom properties.

'

'The default message class for a new StorageItem is IPM.Storage. If the StorageItem existed as a hidden message in a version of Outlook prior to Microsoft Office Outlook 2007, the message class will remain unchanged. In order to prevent modification of the message class, StorageItem does not expose an explicit MessageClass property.

Check Outlook Drafts folder for messages using Outlook VBA

Programatticaly check for draft messages using Outlook VBA

Sub Check_Drafts_Folder()

Dim oNS As Outlook.NameSpace
Dim oFld As Outlook.Folder
Dim oItems As Outlook.Items

On Error GoTo OL_Error

Set oNS = Application.GetNamespace("MAPI")


Set oFld = oNS.GetDefaultFolder(olFolderDrafts)

Set oItems = oFld.Items

If oItems.Count <> 0 Then
MsgBox "There are some messages in the draft"
End If

Exit Sub
OL_Error:
MsgBox Err.Description
Err.Clear
End Sub

The program uses the MAPI Namespace and Draft DefaultFolder.

The only supported name space type is "MAPI". The GetNameSpace method is functionally equivalent to the Session property, which was introduced in Microsoft Outlook 98.

A Folder object that represents the default folder of the requested type for the current profile. If the default folder of the requested type does not exist, for example, because olFolderManagedEmail is specified as the FolderType but the Managed Folders group has not been deployed, then GetDefaultFolder will return Null (Nothing in Visual Basic).

VBA Email Automation / VBA Mail Automation

VBA Email Automation / VBA Mail Automation

Sub Send_Mail_From_Excel()


' This is an automatic mail program. It takes the mail Id's from activeworkbook and uses outlook object to send mail
' The format of the workbook should be as follows
' 1. Data Should start from Row 2 - Sheet 1
' 2. Salutation in Col 1 -e.g., Mr, Ms, Dr etc
' 3. Name in Col 2 -e.g., Sheetal
' 4. Email in Col 4 -e.g., sheetal@vbadud.com
' Program will loop through the entire sheet and send mails to all


Dim oXlWkBk As Excel.Workbook ' Excel Work Book Object

Dim oOLApp As Outlook.Application
Dim oOLMail As MailItem

Dim lRow As Long
Dim olMailItem

Dim sMailID As String
Dim sSalutation As String
Dim sName As String
Dim sDetails As String
Dim sSubject As String
On Error GoTo Err_Trap


Set oXlWkBk = ActiveWorkbook

If oXlWkBk.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row < oolapp =" New" lrow =" 2" oolmail =" oOLApp.CreateItem(olMailItem)" ssalutation =" oXlWkBk.Sheets(1).Cells(lRow," sname =" oXlWkBk.Sheets(1).Cells(lRow," sdetails = "Hi"> 0 And LenB(Trim$(sSalutation)) <> 0) Then
sDetails = sSalutation & " " & sName
ElseIf LenB(Trim$(sName)) <> 0 Then
sDetails = sName
Else
sDetails = "Hi"
End If
sDetails = sDetails & vbNewLine & vbNewLine

sMailID = Trim$(oXlWkBk.Sheets(1).Cells(lRow, 3).Value)

' --- Validate EMail ID
If InStr(1, sMailID, "@") = 0 Then
GoTo TakeNextRow
End If

' Create Mail
With oOLMail
.To = sMailID
.Subject = sSubject
.Body = sDetails & "This is a test mail from VBA Tips & Tricks (http://vbadud.blogspot.com/)"
End With

oOLMail.Send

TakeNextRow:
Next lRow

oXlWkBk.Close (False)

'--------------------------------------------------------
' Coded by Shasur for http://vbadud.blogspot.com
'--------------------------------------------------------


Destroy_Objects:
'Destroy Objects
If Not oOLApp Is Nothing Then Set oOLApp = Nothing


Err_Trap:
' Error Handling
If Err <> 0 Then
MsgBox Err.Description, vbInformation, "VBADUD AutoMail"
Err.Clear
GoTo Destroy_Objects
End If

'------------------------------------------------------------------------------------
' Disclaimer: VBA Tips & Tricks (http://vbadud.blogspot.com) publishes this content
' for the intention of sharing technical knowledge. Any misuse of this program (e.g., spamming)
' will not be our responsibility.
'------------------------------------------------------------------------------------


End Sub


'Keywords: 'Keywords: Automate Email, VBA Email, Send Email from Excel, VBA Mail automation, Mail Automation, Outlook VBA, Automate Outlook, Send Mail from Outlook, Link Excel with Outlook,Microsoft Outlook Mail Automation, Excel VBA Mail, MAPI, Send Multiple eMails





If you want to try the same using Lotus Notes refer http://vbadud.blogspot.com/2007/10/automate-lotus-notes-email-using-visual.html