Thursday, August 30, 2012

Wednesday, August 29, 2012

VBA Excel - Send Outlook mail with attachment

Sub SendWithAtt()
'Reference Object Library : Microsoft Oulook 12.x Object library

    Dim olApp           As Outlook.Application
    Dim olMail          As MailItem
    Dim CurrFile        As String
    Dim AttachmentPPT   As String

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    AttachmentPPT = Application.GetOpenFilename("Presentation (*.ppt), *.ppt")
    With olMail
        .To = Sheet1.Cells(1, 1) ' Or direct email address
'kannaxp@gmail.com
        .CC = Form1.TextBox1
        .Subject = Form1.Combo_ProjectVertical & Form1.lblUser
        .Body = Sheet1.Range("aa1:ab17").Text & vbCrLf
        .Attachments.Add AttachmentPPT
        .Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing
   
MsgBox "Mail successfully sent!", vbInformation, "Mail.."
End Sub