Send via email

[amace87]

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

[jaslake]

Hi qmqce87

Welcome to the Forum!!

Please note, you must use Code Tags around any Code you post. See Rule #3. Please amend your original post to do so.

This link will demonstrate most of what you wish to know about converting workbooks or worksheets to PDF and then email as attachments in Outlook.
If you need help adapting let us know.

http://www.rondebruin.nl/pdf.htm

[amace87]

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Please help.

[amace87]

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

[jaslake]

Hi amace87

Are you using Outlook as your Email client?

[amace87]

I am yes and am using office 2013.

[amace87]

yes I am and office 2013.

[jaslake]

Do you wish to send the entire workbook as a PDF or just the active sheet?

[amace87]

Do you wish to send the entire workbook as a PDF or just the active sheet?

well. Do I need to send the sheet at all. All I need is the code to be adapted to send it in PDF format rather than xml format. At the moment the code looks for an email address in cell a1 on all sheets. if there is an email address there it will send that sheet to the recipient. However it sends it in xml format and I wish for it to be sent in PDF.

[jaslake]

So, you wish to cycle through all the worksheets, if there's an Email Address in Cell A1, create a PDF of the Sheet and Email it to the Address in Cell A1.

Correct?

[amace87]

that's correct.

[jaslake]

okydoke...I'll work something up for you.

[amace87]

I would really appreciate that. Thank you.

[jaslake]

Hi amace87

This Code is directly from the Link I previously referred you to. It's not been at all modified. Place this Code in a General Module of your Workbook. Run the Macro Sub Mail_Every_Worksheet_With_Address_In_A1_PDF()

Please Login or Register  to view this content.

The Code is set to Display the Email Messages...if you wish to Send change this line of Code

Please Login or Register  to view this content.

[amace87]

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?

[jaslake]

Hi amace87

Place your message box code as indicated

Please Login or Register  to view this content.

[amace87]

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

[jaslake]

You're welcome...glad I could help.