sheet to pdf --> email in excel2007

[Mötley]

Hello, I'm new here..

I've recorded a macro that selects specific range in sheets, selects PDF-printer and opens save as-box, user can save range as pdf.

this is the code

Sub toPDF()
'
' toPDF Makro
'
'
    ActiveWindow.SmallScroll Down:=24
    Range("A28:D80").Select
    ActiveWindow.SmallScroll Down:=-30
    Application.ActivePrinter = "PDF-XChange 3.0 porttiin Ne00:"
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,1,""PDF-XChange 3.0 porttiin Ne00:"",,,,FALSE)"
End Sub

code is added to commandbutton.

I need to send this range to my co-workers, daily. I need only one email address, and it's different each day. I've wrote addresses on sheet where user can select wanted address.

I'm using a code I found from Ron de Bruins' site. I've modified it that it opens a messagebox that asks user to select address from cell.

Sub Mail_Range()
'Working in 2000-2010
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim i As Long
    Dim Recipient As String
    Dim r As Range
    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("A28:d65").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, " & _
               "please correct and try again.", vbOKOnly
        Exit Sub
    End If
    On Error Resume Next
    Set r = Application.InputBox("Choose address", Type:=8)
 On Error GoTo 0
 If r Is Nothing Then Exit Sub
    Recipient = r.Value
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)
    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Range of " & wb.Name & " " _
                 & Format(Now, "dd-mmm-yy")
    If Val(Application.Version) < 12 Then
        'You use Excel 2000-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2010
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If
    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        For i = 1 To 3
            .SendMail Recipient, _
                      "Report"
            If Err.Number = 0 Then Exit For
        Next i
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

OK, the big question is;

Is it possible that when user runs macro, it converts range into .pdf, then opens a messagebox (look bold code above) where user can select wanted address, click OK and then pdf-file is sent. Is this possible?

I tried to record a macro that does all the explained commands, saves file as pdf, and then opens an email application with range automatically attached to it. I think this could be THE final option, but the problem is that user needs to delete emailed file manually every time. Is it possible that when user has sent email, file automatically deletes itself?

And I'm NOT using outlook application, (if so, ron de bruins' codes would've saved my life for a long time ago)

thanks in advance!

 

[daverunt]

Here's part of your answer. You can use this InputBox to select an address from a list in B1 to B5 on sheet2. The user need only enter the number. You can adapt the range/sheet for your address list

If you aren't using Outlook have a look at CDO mail on Rons site.

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

' email addresses B1 - B5
For i = 1 To 5
   If Sheets("Sheet2").Range("B" & i).Value <> "" Then
   InputboxList = InputboxList & i & " - " & Sheets("Sheet2").Range("B" & i).Value & " " & vbCr
   End If
Next
   NumSelect = InputBox("Enter  number to choose." & vbCr & vbCr & InputboxList)
   mailName = Range("B" & NumSelect).Value
 
 
(.To = mailName)

 

[Mötley]

Thanks for quick response, but I'm a rookie in VBA, could you please tell me where do I put the code? I do understand macros n' stuff but writing codes, not good handling 'em

 

[daverunt]

I think if you replace the following bit with the code I posted.

On Error Resume Next
    [B]Set r = Application.InputBox("Choose address", Type:=8)[/B]
[B]On Error GoTo 0[/B]
[B]If r Is Nothing Then Exit Sub[/B]
    Recipient = r.Value
 
 
-change
and .sendmail mailname ,  "Report"

How are you sending the mail?
Are you using Ron De Bruins sendmail?
I've never used it. Doesn't it require Outlook?

 

[Mötley]

Yeah the code above, on my first post is from ron de bruins' site, it allows me to send emails easily and outlook is not necessary, i don't have it. I use this code but I need to modify it little bit that it'll let me send pdf

 

[daverunt]

I would look at Ron PDF mail examples.

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

 

[Mötley]

I did, but most of ron's codes need outlook so I can't use them. That code above is only one I can use

 

[Mötley]

I think all do it easy way..

Could you tell me what do I need to change/add so that emailed file automatically deletes.

I use this code, it saves file and right after this opens email application, then user can send it whom ever he wants. And after this, file needs to be deleted.

Sub toPDF()
'
' toPDF Makro
'
'
    ActiveWindow.SmallScroll Down:=24
    Range("A28:D80").Select
    ActiveWindow.SmallScroll Down:=-30
    Application.ActivePrinter = "PDF-XChange 3.0 porttiin Ne00:"
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,1,""PDF-XChange 3.0 porttiin Ne00:"",,,,FALSE)"
End Sub

 

[daverunt]

To delete a file you need to know the physical path/filename or create a variable for it when it is created/saved to then use Kill.

I'm not sure how you can extract it from this PDF-XChange 3.0 printer driver if it is calling the dialog to save the file.

Perhaps someone else has an idea?

 

[Mötley]

I got it!

I recorded it like this, I opened PDF printer options, there's an option "Save" where I selected "Send as email, do not save". Also, there's an option "Email" where I selected "Send using email application".

This is how it works, when I run macro, it turns wanted range into PDF, opens email application with this PDF-file as attachment, I write address and then send it. I just need to store few addresses and I'm done