Solved

Adapting Code for File Save as PDF and Email (use email from cell value)

Posted on 2013-06-27
3
568 Views
Last Modified: 2013-06-28
Hi,

I am using Ron de Bruin code to save a file as a PDF and Email. I made a donation to his site and have given full credit in the body of code. I was able to modify the code to fit my needs with 1 exception and 1 question.

First and most important. I don't use a MAC, but the person I am helping with this project will be using a MAC laptop.  Will this code work for a MAC? If it won't, then don't need to worry about the 2nd part.
'Kathy uses a MAC Laptop, will it work with this file structure?
    FileName = RDB_Create_PDF(ActiveWorkbook, "C:\Users\Brent\Test\YourPdfFile.pdf", True, False)

Open in new window


2nd Question has to do with the email address. The code is set up to email a static address
(in this example it is "bvanscoy678@gmail.com)
If FileName <> "" Then
        RDB_Mail_PDF_Outlook FileName, "bvanscoy678@gmail.com", "This is the subject", _
                             "2013 Fitness Test Results" _
                           & vbNewLine & vbNewLine & "Thanks, Kathy Brenner", False
    Else

Open in new window


Instead I would like to create a variable and have the variable equal the email address in DisplayandPrint! J2  (I will be using a lookup to populate the cell from a list of email addresses). The process will be choose their name from the combo box and send them an PDF file of their results. If I create the variable and have it equal the value of the cell I want, I am not sure how to change the above code to send it to that email address.

I looked at another part of Ron's code that sends an email listed in a cell, but I could not understand what it was doing.

Thanks for any help!! Brent

Ron's Code and Functions

'/////////////////////////////////////////////////////////////
'Code created by Ron de Bruin copyright 2013  http://www.rondebruin.nl/win/s5/pdf.htm
'donated to ron's site

'Note: The macro's in this module call the functions in the "FunctionsModule"
'Be sure that you also copy the code from this module if you want to use it in your own workbook.

Sub RDB_Workbook_To_PDF_And_Create_Mail()
    Dim FileName As String

    'Call the function with the correct arguments
    'Kathy uses a MAC Laptop, will it work with this file structure?
    FileName = RDB_Create_PDF(ActiveWorkbook, "C:\Users\Brent\Test\YourPdfFile.pdf", True, False)

    'RDB_Create_PDF(ActiveWorkbook, "", True, False)
'/////////////////////////I copied and pasted in previous line of code. This way it will create a temp file and kathy won't need to print it each time.
    'For a fixed file name and overwrite it each time you run the macro use
    'RDB_Create_PDF(ActiveWorkbook, "C:\Users\Ron\Test\YourPdfFile.pdf", True, False)

    If FileName <> "" Then
        RDB_Mail_PDF_Outlook FileName, "bvanscoy678@gmail.com", "This is the subject", _
                             "2013 Fitness Test Results" _
                           & vbNewLine & vbNewLine & "Thanks, Kathy Brenner", False
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & 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 Sub
////////////////////
''''''Function

Option Explicit

'The code below are used by the macros in the other two modules
'Do not change the code in the functions in this module

Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
                        OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the pdf
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                                                  Title:="Create PDF")

            'If you cancel this dialog Exit the function
            If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If

        'If OverwriteIfFileExist = False we test if the PDF
        'already exist in the folder and Exit the function if that is True
        If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If

        'Now the file name is correct we Publish to PDF
        On Error Resume Next
        Myvar.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0

        'If Publish is Ok the function will return the file name
        If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
    End If
End Function

Open in new window

EE-MAC-and-EMAIL-address.xlsm
0
Comment
Question by:bvanscoy678
  • 3
3 Comments
 

Author Comment

by:bvanscoy678
Comment Utility
After reading further on Ron's site, there is an example to test for an email in a cell of each worksheet. Since I only want one sheet, I might be able to use this, but don't quite understand it.
'Loop through every worksheet
    For Each sh In ThisWorkbook.Worksheets
        FileName = ""

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

            'If there is a mail address in A1 create the file name and the PDF
            TempFileName = TempFilePath & "Sheet " & sh.Name & " of " _
                         & ThisWorkbook.Name & " " _
                         & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"

            FileName = RDB_Create_PDF(sh, TempFileName, True, False)


            'If publishing is OK create the mail
            If FileName <> "" Then
                RDB_Mail_PDF_Outlook FileName, sh.Range("A1").Value, "This is the subject", _
                                     "See the attached PDF file with the last figures" _
                                   & vbNewLine & vbNewLine & "Regards Ron de bruin", False

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

Open in new window


Since I will already have a file name, maybe I could just use the 2nd portion.

If FileName <> "" Then
                RDB_Mail_PDF_Outlook FileName, sh.Range("A1").Value, "This is the subject", _
                                     "See the attached PDF file with the last figures" _
                                   & vbNewLine & vbNewLine & "Regards Ron de bruin", False

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

Open in new window


Still reading....Thanks
0
 

Accepted Solution

by:
bvanscoy678 earned 0 total points
Comment Utility
I found a much simpler way to do this. I copy and pasted the range into an email.

Thanks, Brent
0
 

Author Closing Comment

by:bvanscoy678
Comment Utility
I am not sure if this is a correct way to close out a question. Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now