VBA export PDF, attach to email and send email

[Sweedler]

Hello

I am using the following code to try to achieve this, but I am running into error issues. I have made the error area in bold text. I am hoping that someone can help me.

Sub Indi_SV_generator()

Dim OutApp As Object
Dim Outmail As Object

Set OutApp = CreateObject("outlook.application")
Set Outmail = OutApp.CreateItem(0)

Dim bolaget As String
Dim plats As String
Dim namn As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim i As Integer
Dim a As Integer


Dim filename As Range
Dim savelocation As String


bolaget = Sheet6.Range("K1")
plats = Sheet6.Range("K2")
year = Sheet6.Range("K4")
month = Sheet6.Range("K5")
day = Sheet6.Range("K6")
a = Sheet6.Range("K7")

For i = a To 2 Step -1

namn = Sheet6.Cells(i, 1)
Sheet6.Range("K8") = Sheet6.Cells(i, 1)

    Set filename = Sheet4.Range("A1")
    savelocation = "C:\Users\marcu\Desktop\EXCELLENT HELP - PC\Customers\Moll Wenden\Test folder\ " & "Individuellt - " & namn & " - " & year & "-" & month & "-" & day
   
Sheets("INDI SV").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    filename:=savelocation

With Outmail

    .to = Sheet6.Cells(i, 2)
    .Subject = "RÄTTNING"
    .Body = "vänligen se bifogad fil"
    .attachments.Add (savelocation & ".pdf")

    .display
   
End With

   
Next i

End Sub

The PDF export works flawlessly, but I the export to email is not working.

Hoping that you can help me,
Cheers

 

[Logit]

Compare this edited code to yours and see the differences. This edited version works here.

Sub Indi_SV_generator()

Dim OutApp As Object
Dim Outmail As Object

Set OutApp = CreateObject("outlook.application")
Set Outmail = OutApp.CreateItem(0)

Dim bolaget As String
Dim plats As String
Dim namn As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim i As Integer
Dim a As Integer


Dim filename As Range
Dim savelocation As String


bolaget = Sheet1.Range("K1")
plats = Sheet1.Range("K2")
year = Sheet1.Range("K4")
month = Sheet1.Range("K5")
day = Sheet1.Range("K6")
a = Sheet1.Range("K7")

On Error Resume Next


For i = a To 2 Step -1

namn = Sheet1.Cells(i, 1)
Sheet1.Range("K8") = Sheet1.Cells(i, 1)

Set filename = Sheet2.Range("A1")
savelocation = "C:\Users\logit\OneDrive\Desktop\ " & "Individuellt - " & namn & " - " & year & "-" & month & "-" & day & ".pdf"

Sheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=savelocation

With Outmail

.to = Sheet1.Cells(i, 2).Value
.Subject = "RÄTTNING"
.Body = "vänligen se bifogad fil"
.attachments.Add (savelocation)

.display

End With


Next i

End Sub

 

[Sequoyah]

Hi all,
the line of code

Set Outmail = OutApp.CreateItem(0)

should be inside the loop

For i = a To 2 Step -1
Set Outmail = OutApp.CreateItem(0)

 

[Peter_SSs]

@Sweedler
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Sweedler]

I have taken both @Sequoyah and @Logit advice and now use the following code. But I still get an error on the same line:

activex component can't create object 429

Sub Indi_SV_generator()

Dim OutApp As Object
Dim Outmail As Object

Set OutApp = CreateObject("outlook.application")

Dim bolaget As String
Dim plats As String
Dim namn As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim i As Integer
Dim a As Integer

Dim filename As Range
Dim savelocation As String

bolaget = Sheet1.Range("K1")
plats = Sheet1.Range("K2")
year = Sheet1.Range("K4")
month = Sheet1.Range("K5")
day = Sheet1.Range("K6")
a = Sheet1.Range("K7")

On Error Resume Next

For i = a To 2 Step -1
Set Outmail = OutApp.CreateItem(0)

namn = Sheet1.Cells(i, 1)
Sheet1.Range("K8") = Sheet1.Cells(i, 1)

Set filename = Sheet2.Range("A1")
savelocation = "C:\Users\logit\OneDrive\Desktop\ " & "Individuellt - " & namn & " - " & year & "-" & month & "-" & day & ".pdf"

Sheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=savelocation

'Set OutApp = CreateObject("outlook.application")


With Outmail

.to = Sheet1.Cells(i, 2).Value
.Subject = "RÄTTNING"
.Body = "vänligen se bifogad fil"
.attachments.Add (savelocation)

.display

End With

Next i

End Sub

 

[Peter_SSs]

I have taken both @Sequoyah and @Logit advice

.. but not mine.
If you want to continue to get free help from the forum I suggest that you make it as easy as possible for people to give that help. That is what my request was aimed at.

 

[Sweedler]

Hello @Peter_SSs

I'm sorry that I did not understand your first comment to me. Is this correct now?

Sub Indi_SV_generator()

Dim OutApp As Object
Dim Outmail As Object

[B]Set OutApp = CreateObject("outlook.application")[/B]

Dim bolaget As String
Dim plats As String
Dim namn As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim i As Integer
Dim a As Integer

Dim filename As Range
Dim savelocation As String

bolaget = Sheet1.Range("K1")
plats = Sheet1.Range("K2")
year = Sheet1.Range("K4")
month = Sheet1.Range("K5")
day = Sheet1.Range("K6")
a = Sheet1.Range("K7")

On Error Resume Next

For i = a To 2 Step -1
Set Outmail = OutApp.CreateItem(0)

namn = Sheet1.Cells(i, 1)
Sheet1.Range("K8") = Sheet1.Cells(i, 1)

Set filename = Sheet2.Range("A1")
savelocation = "C:\Users\logit\OneDrive\Desktop\ " & "Individuellt - " & namn & " - " & year & "-" & month & "-" & day & ".pdf"

Sheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=savelocation

'Set OutApp = CreateObject("outlook.application")


With Outmail

.to = Sheet1.Cells(i, 2).Value
.Subject = "RÄTTNING"
.Body = "vänligen se bifogad fil"
.attachments.Add (savelocation)

.display

End With

Next i

End Sub

 

[Logit]

The following works here. You will need to change the SAVE LOCATION to match your computer. Presently the path is set for my computer here.

Option Explicit

Sub Indi_SV_generator()

Dim OutApp As Object
Dim Outmail As Object

Set OutApp = CreateObject("outlook.application")
Set Outmail = OutApp.CreateItem(0)

Dim bolaget As String
Dim plats As String
Dim namn As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim i As Integer
Dim a As Integer

Dim filename As Range
Dim savelocation As String

    bolaget = Sheet1.Range("K1")
    plats = Sheet1.Range("K2")
    year = Sheet1.Range("K4")
    month = Sheet1.Range("K5")
    day = Sheet1.Range("K6")
    a = Sheet1.Range("K7")
    
    On Error Resume Next
    
        For i = a To 2 Step -1
        
        
        namn = Sheet1.Cells(i, 1)
        Sheet1.Range("K8") = Sheet1.Cells(i, 1)
        Set filename = Sheet2.Range("A1")
        savelocation = "C:\Users\logit\OneDrive\Desktop\ " & "Individuellt - " & namn & " - " & year & "-" & month & "-" & day & ".pdf"
        
        Sheets("Sheet2").Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        filename:=savelocation
        
        
            With Outmail
            
                .To = Sheet1.Cells(i, 2).Value
                .Subject = "RÄTTNING"
                .Body = "vänligen se bifogad fil"
                .attachments.Add (savelocation)
                
                .display
            
            End With
        
        Next i

End Sub

 

[Sweedler]

Hello

This seems to be one step in the right direction as the Macro is running now, and it does generate the PDF files. What it does not do is produce and send emails. So there must be something still missing.

I have been reading around trying to figure this out elsewhere as well, and one thing that keeps getting mentioned is that "NEW" Outlook is not compatible to be run from a MACRO. Have you heard anything like this? I am unable to find outlook under tools and references in the Developer app.

Please help

 

[Logit]

This is what I have selected here. You can download my workbook from :

Internxt Drive – Private & Secure Cloud Storage

Remember to change the path to match your computer.