Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 13th, 2010, 04:26 AM
Registered User
Join Date: May 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mailing PDF from EXCEL


I have clarification while converting excel as pdf and sending email through outlook, I have tried few ways but the code which given below checks for email address in each sheet and prompt for mail, can anyone correct the below code for converting the data from active worksheet or particular sheet instead looping through the all sheets.

Thanks in advance


Sub FormT_Picture9_Click()
Dim sh As Worksheet
Dim TempFilePath As String
Dim TempFileName As String
Dim FileName As String

'Temporary path to save the PDF files
'You can also use another folder like
'TempFilePath = "C:\Users\Ron\MyFolder\"
TempFilePath = Environ$("temp") & "\"

'Loop through every worksheet
For Each sh In ThisWorkbook.Worksheets
FileName = ""

'Test A1 for a mail address
If sh.Range("d2").Value Like "?*@?*.?*" Then

'If there is a mail address in A1 create the file name and the PDF
TempFileName = TempFilePath & "Form T " & ".pdf"

FileName = GVR_Create_PDF(Range("a2:AC23"), TempFileName, True, False)

'If publishing is OK create the mail
If FileName <> "" Then
GVR_Mail_PDF_Outlook FileName, sh.Range("d2").Value, "FORM T", _
"Hi" & " " & vbNewLine & vbNewLine & "Please find the attached Form T for the year " _
& vbNewLine & vbNewLine & vbNewLine & vbNewLine & "GV Reddy", False

'After the mail is created delete the PDF file in TempFilePath
If Dir(TempFileName) <> "" Then Kill TempFileName

MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If

End If
Next sh
End Sub
Old July 15th, 2010, 07:23 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

You can replace the for loop

For Each sh In ThisWorkbook.Worksheets

with the following statement

Set sh = ThisWorkbook.Worksheets(1)


Set sh = ThisWorkbook.Worksheets('Sheet1')


Set sh = Activesheet

C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Excel to PDF shivavalkyre Beginning VB 6 1 March 15th, 2007 03:44 PM
Excel to PDF through VB dpkbahuguna Beginning VB 6 3 September 13th, 2006 02:59 PM
Need help w/ creating PDF files in Excel ArtDecade VB.NET 2002/2003 Basics 2 June 14th, 2006 11:06 PM
excel to pdf dpkbahuguna Beginning VB 6 3 May 30th, 2006 07:00 AM
convert PDF file to XLS for Excel crmpicco Classic ASP Basics 2 November 30th, 2005 05:51 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.