PDA

View Full Version : Help Needed, E-mailing reports



alopecito
04-10-2009, 09:25 AM
Hi, Again
I need help writing a code, I need to e-mail a report, the problem I have is that is very time consuming as I'm doing it manually, I split the sales weekly report in different tabs, each tab for each sales rep,
I need to e-mail each tab accordingly with their respective e-mailing addresses(that info appears in the formulas tab) and name the tab or file sent accordingly to the rep whose report is being sent to.

I don't know if this can be done?
Any ideas will help!

Thanks

georgiboy
04-11-2009, 01:57 AM
Something like this maybe...

You will need to put your email addresses in column D on the lookup sheet because vlookup will only return a column after the one in wich it found the data in.

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
Dim body_ As String
Dim attach_ As String
Dim Sh As Worksheet

Set OutlookApp = CreateObject("Outlook.Application")

For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "E-mailing add" Then

Sh.Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "Example.xls"

email_ = WorksheetFunction.VLookup(Sh.Name, Sheet1.Range("C2:D500"), 2, False)
subject_ = Sh.Name
body_ = "Here is your sheet" & vbNewLine

Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
.Subject = subject_
.Body = body_
.Attachments.Add ActiveWorkbook.FullName
.Send
End With

ActiveWorkbook.Close False

Kill ThisWorkbook.Path & "\" & "Example.xls"

End If
Next

End Sub
Hope this helps