Snasui Excel Forum

Code: Select all

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim flname As String
Dim wkb As Workbook

flname = VBA.Environ("temp") & "\" & VBA.Format(VBA.Now, "dd_mm_yyy_hh_mm_ss") & ".xlsx"
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

Set wkb = Workbooks.Add ' add new workbook
' change sheet name and range here
ThisWorkbook.Sheets(1).Range("A1:g25").Copy Destination:=wkb.Sheets(1).Range("a1")
wkb.SaveAs flname ' save workbook with temp name
wkb.Close 'close it

With olMail
    .To = "abc@gmail.com"
    .Subject = "Hello"
    '  vbNewLine  is used to insert a row
    .Body = "Dear " & vbNewLine & "Please find the attachment" & vbNewLine & vbNewLine & vbNewLine & "Regards" & vbNewLine & "email"
    .Attachments.Add flname  ' attach the workbook
    .Display ' or use .send
End With

Set wkb = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub 

Code: Select all

Dim r As Range
'Other code
With thisworksheet
    For Each r In .Range("o3", .Range("o" & .Rows.Count).End(xlUp))
        With olMail
            '.To = "abc@gmail.com"
            .To = r.Value
            .Subject = "Hello"
            '  vbNewLine  is used to insert a row
            .Body = "Dear " & vbNewLine & "Please find the attachment" & vbNewLine & vbNewLine & vbNewLine & "Regards" & vbNewLine & "email"
            .Attachments.Add flname  ' attach the workbook
            .Display ' or use .send
        End With
    Next r
    'Other code
End With