0

Right now I have a list of persons in a sheet that is called "gebruikers". Every user has an email address in this list.

What I do now is go through that list automatically and save the file as an PDF for every person on the list.

Is there any possible way to save the file like it does now and then automatically send that file to their email address?

Here is my code now:

Public Sub pdfopslag()

    Dim myCell              As Range
    Dim valRules            As Range
    path = Range("I21").Text
    Set valRules = Evaluate(Range("A9").Validation.Formula1)
    Application.ScreenUpdating = True
    Worksheets("factuur").UsedRange.Columns("A:G").Calculate
    For Each myCell In valRules
        emailadres = Application.WorksheetFunction.VLookup(Sheets("factuur").Range("A9"), Sheets("Gegevens").Range("A1:G6"), 7, False)

        Range("A9") = myCell
        filename1 = Range("B18").Text
        filename2 = Range("A8").Text

        PDF_File = path & filename1 & " " & filename2 & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=PDF_File
        On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)

    ' Prepare e-mail
    .Subject = "test"
    .To = emailadres
    .Attachments.Add = PDF_File

    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0

  End With
    Next myCell
Set OutlApp = Nothing
End Sub

i only dont know what i need to putt behind .attachments.add because right now it gives an error 440

Klaas
  • 39
  • 1
  • 1
  • 8
  • 1
    Possible duplicate of [Sending Email attachments from excel via VBA](https://stackoverflow.com/questions/37302602/sending-email-attachments-from-excel-via-vba) – dwirony May 07 '18 at 17:48
  • @dwirony that is the file itself i need to send a file that is saved in the same macro – Klaas May 07 '18 at 17:51
  • What's the difference? After you save the file, you create a new outlook item, attach the file, send to the email address. Rinse and repeat. I believe it's the same process :) – dwirony May 07 '18 at 17:52
  • You can use the macro recorder to see what the code looks like to save as a pdf, then you can edit the code similar to what you have in your code to "Exportas" the range name – Davesexcel May 07 '18 at 17:56
  • @dwirony right now i have that it creates an email but i dont know how to add a file to it that was just saved since it isnt a variable – Klaas May 07 '18 at 18:31
  • 1
    @Klaas You still need to **save** the file somewhere before you can attach it. If you don't want to keep the file after attaching it and sending it, you can use `Kill` to get rid of it. – dwirony May 07 '18 at 20:28

0 Answers0