Send via email

OK so is this what you were after with rule #3. I do apologise I am new to this. So the below code is what I need adjusting to be able to send PDF instead of xml format. I aleady have a pdf code I just cant figure out how to combine them in order to keep the original code but have it send as pdf instead. please advise.

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

That was the code to keep and I sort of need the below code to make it pdf.

Sub Email_ActiveSheet_As_PDF()

'Do not forget to change the email ID
'before running this code

    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String

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

' Temporary file path where pdf
' file will be saved before
' sending it in email by attaching it.

    TempFilePath = Environ$("temp") & "\"

' Now append a date and time stamp
' in your pdf file name. Naming convention
' can be changed based on your requirement.

    TempFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"

'Complete path of the file where it is saved
    FileFullPath = TempFilePath & TempFileName

'Now Export the Activesshet as PDF with the given File Name and path

    On Error GoTo err
    With ActiveSheet
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With

'Now open a new mail

    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)

    On Error Resume Next
    With NewMail
        .To = "[email protected]"
        .CC = "[email protected]"
        .BCC = "[email protected]"
        .Subject = "Type your Subject here"
        .Body = "Type the Body of your mail"
        .Attachments.Add FileFullPath '--- full path of the pdf where it is saved
        .Send   'or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0

'Since mail has been sent with the attachment
'Now delete the pdf file from the temp folder

    Kill FileFullPath

'set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing

'Now set the application properties back to true
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox ("Email has been Sent Successfully")
    Exit Sub
err:
        MsgBox err.Description

End Sub

Please help.

I really need help with the above guys. If someone else could take a look.

I am yes and am using office 2013.

yes I am and office 2013.

that's brilliant. Thanks. one last thing. Is there anyway I could add a message box like my first code when the operation is complete. I try to add it at the end but it comes up after every sheet. Is there somewhere specific in the code I could put it to prevent it from doing this?

Absolutely Superb. Thanks for all your help. Exactly what I need. Thanks again.