Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old August 10th, 2011, 08:57 AM
Registered User
 
Join Date: Aug 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Save and attach sheet as PDF

Hi!!

I'm trying save the first sheet of my workbook as a pdf then attach to an outlook email then view the email before sending. Everything works except for getting the pdf to attach. My code is all activiated by the button click event. Range("T2") is the file path where it is saved. It's

="S:\Common\MANUALS\QUALITY\IS\Notes\Furnace Notes"&TEXT(H1,"mmddyy")

the part of the code in bold seems to be the only part not working\

Please help!! thanks

Option Explicit
Sub SaveBtn_Click()
Dim filename As String
ThisFile = Range("T2").Value
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=ThisFile, _
OpenAfterPublish:=True
End With
Mail
End Sub

Sub Mail()
Dim filename As String
RDB_Mail_PDF_Outlook filename, "name@email.com", "Furnace Notes and Summary" & Range("H1"), "This is a summary of today's meeting" & vbNewLine & vbNewLine & "My name", False
End Sub

Function RDB_Mail_PDF_Outlook(filenamepdf As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = StrTo
.CC = ""
.BCC = ""
.Subject = StrSubject
.Body = StrBody
.Attachments.Add filenamepdf
.Display
End If
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Function
  #2 (permalink)  
Old August 16th, 2011, 05:56 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Is it throwing any error or simply the PDF is not getting attached.

Also check if the filename contain any extra spaces etc (that gets added from the Excel cell), if that is the case you need todo a trim

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

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
  #3 (permalink)  
Old August 16th, 2011, 09:14 AM
Registered User
 
Join Date: Aug 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi! thanks for replying

I dont get any errors at all. When I press the button with the code behind it the email pops up from outlook with all the email addresses filled in, the subject filled in, and the message filled in but no attachment so its going through the code just not doing anything with it.

How do you do a trim? I'm not very familiar with excel vb :/

thanks!
  #4 (permalink)  
Old August 16th, 2011, 09:32 AM
Registered User
 
Join Date: Aug 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok so I tried the trim thing and this is what it looks like in my code but it still doesn't work :(.


Sub SaveBtn_Click()
Dim FileName As String
Dim mypdf As String
Dim ThisFile As String
ThisFile = Range("T2").Value
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=ThisFile, _
OpenAfterPublish:=True
End With
FileName = Trim(ThisFile) & ".pdf"
If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, "email@email.email; email1@email1.email2", "Notes" & "_" & Range("H1"), "This is a summary of today's 7am meeting" & vbNewLine & vbNewLine & "name", False
End If
End Sub




Function RDB_Mail_PDF_Outlook(FileName As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = StrTo
.CC = ""
.BCC = ""
.Subject = StrSubject
.Body = StrBody
.Item.Attachments.Add FileName
.Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Function

I'm not sure if I put it in the right place. What do you think?


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
linking information to another sheet and save it monicaspinder Beginning VB 6 1 June 29th, 2007 04:55 AM
how to save as an excel sheet azizsaad19 Beginning VB 6 2 May 25th, 2007 09:48 AM
attach a file and save it in the databas mary25dec Pro JSP 1 December 11th, 2006 04:10 AM
to save datagrid content to a excel sheet josna VB.NET 1 March 4th, 2006 07:10 AM





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