Email from excel-VBA

[uroy]

Hi All,

I have attached one excel workbook(without code) . I want to send email to their personal mail ID from summary page by specific user selection. Cc and Bcc can be added later.

The attachment should be go as per their specific scorecard sheet.
Thanks & Regards option must be their in the VB code.

Advance Thanks
Uday

[mancubus]

hi.

i assume value in F2 and 's Scorecard is the worksheet name.



Sub email_worksheet_as_wb()

Dim ws As Worksheet
Dim TempFilePath As String, TempFileName As String

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
End With

Set ws = ThisWorkbook.Worksheets("Email list")

TempFilePath = Environ$("temp") & "\"
'TempFilePath = ThisWorkbook.Path & "\" 'if you dont have access to temp folder use this line instead of above line
TempFileName = Range("F2").Value & "'s Scorecard"

Worksheets(TempFileName).Copy
ActiveWorkbook.SaveAs TempFilePath & TempFileName, FileFormat:=51

With CreateObject("Outlook.Application")
With .CreateItem(olMailItem)
.To = ws.Range("A2").Value
.CC = ws.Range("B2").Value
.BCC = ws.Range("C2").Value
.Subject = ws.Range("D2").Value
.Body = ws.Range("E2").Value
.Attachments.Add ActiveWorkbook.FullName
.Save
.Display
.Send
End With
End With

ActiveWorkbook.Close False
Kill TempFilePath & TempFileName & ".xlsx"

With Application
.EnableEvents = True
End With

End Sub


recommended reading:
http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#H3
http://www.rondebruin.nl/win/s1/outlook/amail5.htm
http://www.jpsoftwaretech.com/excel-vba/send-worksheets-by-email-as-separate-workbooks/

[uroy]

Excellent...:2jump: It is working like a charm.

[mancubus]

please mark the thread as solved from Thread Tools dropdown. It's above the 1st post.