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!

 

[Mötley]

Oh, one more question. How can I insert pictures cell by cell? First picture on A56, second on A57 and so on...

I'm using this and it works but if it's possible to modify this that I can select/insert more pictures at the same time, I would be very greatful!

Sub ExampleUsage()
 Dim myPicture As String, myRange As Range
 myPicture = Application.GetOpenFilename _
 ("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", _
 , "Select Picture to Import")
 If myPicture = "False" Then
    Exit Sub
End If
 Set myRange = Range("A56:D65")
 InsertAndSizePic myRange, myPicture
End Sub
Sub InsertAndSizePic(Target As Range, PicPath As String)
 Dim p As Picture
 Application.ScreenUpdating = False
 Set p = ActiveSheet.Pictures.Insert(PicPath)
 If Target.Cells.Count = 1 Then Set Target = Target.MergeArea
 With Target
 p.Top = .Top
 p.Left = .Left
 p.Width = .Width
 p.Height = .Height
 p.Placement = xlMoveAndSize
End With
End Sub