Macro to send each worksheet in mail attachment

Gaura215

Board Regular
Joined
Feb 2, 2011
Messages
97
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:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:

1) Firstly, I am facing the debug error in ".SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum". I am confused why is so.
2) Secondly, I am unsure how to handle the character/paragraph spacing using this macro. 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.<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 15.75pt" alt="" type="#_x0000_t75"> <v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/confused.gif" src="file:///D:\DOCUME~1\G7D60~1.KHA\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top