Excel Macro: Send mail using MS Outlook

Recently my friend had a requirement of sending a mail from Excel, so i helped him to do it with a simple code. This code works if Microsoft Outlook is installed in the machine


'mail using the outlook URL format
URL = "mailto:abc@gmail.com?subject=Work Request Recieved.&body=You got some work to do"
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait five seconds(for the compose window to open) before sending keystrokes
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "%s"


 

Outlook Macro – To Add Text to the Mail message

There was requirement for my friend,  where he needs to write a macro which will pop up a form when someone clicks on new mail or reply or forward.

So i did some research and came up with a code, shown below.

 

ThisOutlookSession (code for OutlookSession)
———————————————- ———-

Option Explicit

Dim myTrapper As MyWrapper

Private Sub Application_Startup()
   Set myTrapper = New MyWrapper
End Sub

Private Sub Application_Quit()
   Set myTrapper = Nothing
End Sub

—————————————————-

MyWrapper Class Module
———————-

Option Explicit

Dim WithEvents objInspectors As Outlook.Inspectors
Dim WithEvents objInsp As Outlook.Inspector
Dim WithEvents objMsg As Outlook.MailItem

Private Sub Class_Initialize()
   Set objInspectors = Application.Inspectors
End Sub

Private Sub Class_Terminate()
   Set objInspectors = Nothing
   Set objInsp = Nothing
   Set objMsg = Nothing
End Sub

Private Sub objInspectors_NewInspector(ByVal Inspector As Inspector)
   If (Inspector.CurrentItem.Class = olMail) Then
       Set objMsg = Inspector.CurrentItem
       Set objInsp = Inspector
   End If
End Sub

Private Sub objInsp_Close()
   Set objMsg = Nothing
End Sub

Private Sub objMsg_Open(Cancel As Boolean)
   Set UserForm1.SharedInsp = objInsp
   UserForm1.Show
End Sub

—————————————————-

UserForm Code
—————-

Create Four CheckBoxButtons and one Command Button
————————————————–

Public SharedInsp As Outlook.Inspector

Dim msgItem As Object

Dim msg As String

Private Sub CommandButton1_Click()
      If (msg <> “”) Then
       Set msgItem = SharedInsp.CurrentItem
       msgItem.HTMLBody = “<center><b><u>” & msg & “</u></b></center>” & msgItem.HTMLBody
       Set msgItem = Nothing
       Unload Me
   Else
       MsgBox “Please Choose one of the Options”
   End If
End Sub

Private Sub OptionButton1_Click()
   msg = “Confidential”
End Sub

Private Sub OptionButton2_Click()
   msg = “Internal Material”
End Sub

Private Sub OptionButton3_Click()
   msg = “Highly Confidential”
End Sub

———————————————–

 

Some of the Other events in the mail are

Private Sub objMailItem_AttachmentAdd(ByVal Attachment As Attachment)

End Sub

Private Sub objMailItem_AttachmentRead(ByVal Attachment As Attachment)

End Sub

Private Sub objMailItem_BeforeAttachmentSave(ByVal Attachment As Attachment, Cancel As Boolean)

End Sub

Private Sub objMailItem_BeforeDelete(ByVal Item As Object, Cancel As Boolean)

End Sub

Private Sub objMailItem_Close(Cancel As Boolean)

End Sub

Private Sub objMailItem_Forward(ByVal Forward As Object, Cancel As Boolean)

End Sub

Private Sub objMailItem_Open(Cancel As Boolean)

End Sub

Private Sub objMailItem_PropertyChange(ByVal Name As String)

End Sub

Private Sub objMailItem_Read()

End Sub

Private Sub objMailItem_Reply(ByVal Response As Object, Cancel As Boolean)

End Sub

Private Sub objMailItem_ReplyAll(ByVal Response As Object, Cancel As Boolean)

End Sub

Private Sub objMailItem_Send(Cancel As Boolean)

End Sub

Private Sub objMailItem_Write(Cancel As Boolean)

End Sub

 

Reference Link :

http://www.cimaware.com/resources/article_57.html