Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mailing each worksheet in Workbook
Hello
I have 70-80 worksheets in my workbook, which I need to send to different receipients as an individual workbooks. My codes looks like below: Sub Mail_Every_Worksheet() 'Working in 2000-2010 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007-2010 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") For Each sh In ThisWorkbook.Worksheets If sh.Range("AA65536").Value Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Agent:" & sh.Name & " (" & "107" & ") " & "CEVA USA REPORTS FOR " & Format(Now, "mmm-yy") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .to = sh.Range("AA65536").Value .CC = "" .BCC = "" .Subject = "Testing Automation Script" .Body = "Hi" _ & "The Following PS 107 Report." _ & "The following are reports for the month of " & Format(Now, "mmm-yy") _ & "I am in charge of the distribution of the PS reports;" _ & "should you have any questions/comments regarding the entries in these reports, please" _ & "contact the station in charge of the shipment in question." .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close savechanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub I have two doubts in this: Firstly, I am facing the debug error in ".SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum". I am confused why is so. Secondly, I want the body of the mail like Hi in first line then second line should be like "The Following PS 107 Report." & "The following are reports for the month of " & Format(Now, "mmm-yy") and then leaving the next line blank, it should start a new paragraph as "I am in charge of the distribution of the PS reports; should you have any questions/comments regarding the entries in these reports, please contact the station in charge of the shipment in question." Please someone help.
__________________
Regards Gaurav |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
E-mailing a worksheet | Excel Discussion (Misc queries) | |||
e-mail individual worksheet/tab w/out e-mailing entire workbook? | Excel Discussion (Misc queries) | |||
E-mailing a worksheet from a workbook using Windows Mail | New Users to Excel | |||
mailing one sheet from workbook | Excel Discussion (Misc queries) | |||
Worksheet e-mailing | Excel Worksheet Functions |