Send via email

Hi Guys,

I really hope you can help. I am looking to use the below code. However I do not wish to send it as an excel format I wish to send it as a pdf. Any Ideas?

Option Explicit



Sub Mail_Every_Workbook()

'Working in 97-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 I As Long



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



For Each sh In ThisWorkbook.Worksheets

If sh.Range("A1").Value Like "?*@?*.?*" Then



sh.Copy

Set wb = ActiveWorkbook



TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")



With wb

.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next

For I = 1 To 3
.SendMail sh.Range("A1").Value, _
"Huffkins Rota"
If err.Number = 0 Then Exit For
Next I

On Error GoTo 0

.Close SaveChanges:=False

End With



Kill TempFilePath & TempFileName & FileExtStr



End If

Next sh



With Application

.ScreenUpdating = True

.EnableEvents = True

End With

End Sub