ExcelSujeet (YouTube Blogger)

 Use the below line right after copying & pasting data using VBA. Below line clears the copied data from the clipboard. Application.CutCopyPaste=False Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard.

Early Binding   Dim oApp as Outlook.Application Dim oEmail as Outlook.MailItem Dim oNS as Outlook.NameSpace Set oApp=New Outlook.Application Set oEmail=oApp.CreateItem(olMailItem) Set oNS=oApp.GetNamespace("MAPI")  'Messaging Application Programming Interface oEmail.SendUsingAccout=oNS.Accounts.Item(1) 'To send email from a specific account. Or Use With oEmail     .SentOnBehalfOfName="Enter the specific account email-id." end With Late Binding Dim oApp as Object Dim oEmail as Object Dim oNS as Object Set oApp=CreateObject("Outlook.Application") Set oEmail=oApp.CreateItem(0) Set oNS=oApp.GetNamespace("MAPI") 'Messaging Application Programming Interface oEmail.SendUsingAccount=oNS.Accounts.Item(1) 'To send email from a specific account. Or Use With oEmail     .SentOnBehalfOfName="Enter the specific account email-id." end With

Early Binding First establish the reference with the outlook library by going to 'Tools' Tab then Click on 'References', from the available reference Select the "Microsoft Outlook 16.0 Object Library", 16.0 could be 14.0 or 15.0 basis on the version of the Microsoft Office in your system. Dim oApp as Outlook.Application Dim oEmail as Outlook.MailItem Dim oNS as Outlook.NameSpace Set oApp=New Outlook.Application Set oEmail=oApp.CreateItem(olMailItem) Set oNS=oApp.GetNamespace("MAPI")  oEmail.SendUsingAccout=oNS.Accounts.Item(1) 'To send email from a specific account. Late Binding No need to establish relationship with any library. Dim oApp as Object Dim oEmail as Object Dim oNS as Object Set oApp=CreateObject("Outlook.Application") Set oEmail=oApp.CreateItem(0) Set oNS=oApp.GetNamespace("MAPI") oEmail.SendUsingAccount=oNS.Accounts.Item(1) 'To send email from a specific account.

 Outlook Email Automation Examples 1) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sub SendMail_Bangalore() Dim oApp As Outlook.Application Dim OEmail As Outlook.MailItem Dim signature As String Dim str As String Dim loc As String Set oApp = New Outlook.Application Set OEmail = oApp.CreateItem(olMailItem) Dim olNS As Outlook.Namespace Set olNS = Outlook.Application.GetNamespace("MAPI") OEmail.SendUsingAccount = olNS.Accounts.Item(1) loc = "Bangalore" If Sheet1.Range("b10").Value < 2 Then str = "Hi All," & "<br/><br/>" & "Mentioned Package ID " & Sheet1.Range("b9").Value & " has been renewed for next " & Sheet1.Range("b10").Value _ & " day. Service will start from " & Format(Sheet1.Range("b11").Value, "DD-MMM") & "." & "<br/><br/>" Else str = "Hi All...

Use the below function in your subroutine in the below mentioned way: .HTMLBody = str & rngHTML(Sheet1.Range("a13:g17"))  & .HTMLBody ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Function rngHTML(rng As Range)     Dim fso As Object, ts As Object, TempWB As Workbook     Dim TempFile As String     TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"     '' copy the range and create a new workbook to paste the data into     rng.Copy     Set TempWB = Workbooks.Add(1)     With TempWB.Sheets(1)         .Cells(1).PasteSpecial Paste:=8         .Cells(1).PasteSpecial xlPasteValues, , False, False         .Cells(1).PasteSpecial xlPasteFormats, , False, False         .Cells(1).Select         Application.CutCopyMode = False         O...