Excel/formula

Advertisement


Question
QUESTION: Tom

Yeah i thought i had it figured out i did this code on a sample workbook and it works, sheets are 1 -9

when i brought it over to my workbook i get an error on the array for whatever sheets are called up based on the # in L32 - my sheets are all renamed as defaultfaxcover, defaultParts, etc, instead of Sheet1, Sheet2, etc...would that make the difference - have i coded those forms wrong?

Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
   Dim FileName As String

   Application.ScreenUpdating = False
  
   If (Range("L32").Value) = 1 Then
       
   Worksheets(Array("faxcover", "Parts")).Select
   
   End If
   
   If (Range("L32").Value) = 2 Then
       
   Worksheets(Array("faxcover", "photos")).Select
   
   End If
       
   If (Range("L32").Value) = 3 Then
        
   Worksheets(Array("faxcover", "ServiceRequest")).Select
   
   End If
   
   If (Range("L32").Value) = 4 Then
       
   Worksheets(Array("faxcover", "Locator")).Select
   
   End If
   
   If (Range("L32").Value) = 5 Then
       
   Worksheets(Array("faxcover", "BillBack")).Select
   
   End If
       
   If (Range("L32").Value) = 6 Then
        
   Worksheets(Array("faxcover", "photos", "ServiceRequest")).Select
   
   End If
   
   If (Range("L32").Value) = 7 Then
       
   Worksheets(Array("faxcover", "Locator", "photos")).Select
   
   End If
   
   If (Range("L32").Value) = 8 Then
       
   Worksheets(Array("faxcover", "Locator", "ServiceRequest")).Select
   
   End If
       
   If (Range("L32").Value) = 9 Then
        
   Worksheets(Array("faxcover", "photos", "Locator", "ServiceRequest")).Select
   
   End If
   
   'Worksheets(Array("Sheet6", "Sheet3", "Sheet4", "Sheet5")).Select

   If ActiveWindow.SelectedSheets.Count > 1 Then
       MsgBox "There is more then one sheet selected," & vbNewLine & _
         "be aware that every selected sheet will be published"
   End If

   'Call the function with the correct arguments
   'Tip: You can also use Sheets("YourSheetName") instead of ActiveSheet in the code(sheet not have to be active then)

   FileName = RDB_Create_PDF(Source:=ActiveSheet, _
         FixedFilePathName:="", _
         OverwriteIfFileExist:=True, _
         OpenPDFAfterPublish:=False)

   'For a fixed file name use this in the FixedFilePathName argument
   'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"

   If FileName <> "" Then
       RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
         StrTo:="", _
         StrCC:="", _
         StrBCC:="", _
         StrSubject:="", _
         Signature:=True, _
         Send:=False, _
         StrBody:="<H3>Service Department </H3><br>" & _
         "<body>Attached file is for your review." & _
         "<br><br>" & "Regards,</body>"
   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

amy suggest=ions, if so i would greatly appreciate it

also is there a code i can include for the TO, CC and Subject so they aren't left blank, and they can reference a cell on that page?

Hellllp

Tony

ANSWER: Tony,

Yes, the sheet names in the array must match the sheet names in the workbook.

To construct the email, the routine you show calls a second routine:

RDB_Mail_PDF_Outlook

the second routine supports named arguments which you see in the call

FileNamePDF:=FileName, _
        StrTo:="", _
        StrCC:="", _
        StrBCC:="", _
        StrSubject:="", _
        Signature:=True, _
        Send:=False, _
        StrBody:="<H3>Service Department </H3><br>" & _
        "<body>Attached file is for your review." & _
        "<br><br>" & "Regards,</body>"


so the email address would be placed as the argument to  StrTo:=""   for example
 StrTo:="Tony@gmail.com"   or   StrTo:=Activesheet.Range("A1").Value

but you would need to understand what worksheet you are actually referring to. ActiveSheet may not be appropriate.

This will be passed into   the routine where the code to actually construct the email and possibly send it is located.  


--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: got it almost done...the email and such all worked - just cant figure out how to had things to the body....such as date, formatted reference by a cell...the portion below works good just need help on targeting a cell on the activesheet...

thanks

If FileName <> "" Then
       RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
         StrTo:=ActiveSheet.Range("D9").Value, _
         StrCC:=ActiveSheet.Range("K28").Value, _
         StrBCC:="", _
         StrSubject:=ActiveSheet.Range("L29").Value, _
         Signature:=True, _
         Send:=False, _
         StrBody:="<H3>Service Department </H3><br>" & _
         "<body>Attached file is for your review." & _
         "<br><br>" & "</body>"
   Else

Answer
Tony,

I would guess this builds a text string for the body.  

StrBody:="<H3>Service Department </H3><br>" & _
        "<body>Attached file is for your review." & _
        "<br><br>" & "</body>"


It is in HTML which I don't do but blocks are set off with stuff like  <H3> .... </H3>  

which would be header3 I assume.   if I wanted to put the text in cell A1 after the .. for your review
I would do

StrBody:="<H3>Service Department </H3><br>" & _
        "<body>Attached file is for your review." & _
        Activesheet.Range("A1").Text & _
        "<br><br>" & "</body>"

The _ above are line continuation characters so it just tells the vba interpreter that the code continues on the next line.  It is actually a space and an underscore.

I believe the  <br>  but be a line break in the output - essentially hitting return if you were typing it.

If you want formatting, you would probably have to do that with html commands - like I say, I don't know html.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.