excel to create pdf-s and attach to mail

[shefter]

hello everyone.

im new to the forums as You can see. hope you all take me in nicely.

anyway im working on my first macro and ran into a little trouble. i cant seem to find a solution either.

the idea of my code is to take data (name and email) from one sheet, open up a word template and paste that data (name and email) to specific bookmarks on the template. then save the edited template as in pdf format(different file for each row of name and email). the problem i am having is that i cant seem to get outlook to attach the recently saved pdf file.

another problem im having is closing the winword app after running the macro(i have to close it in task manager)

thanks in advance for help

Sub macro()

Dim objDoc As Word.Document
Dim filepath As String
Dim nimi As String
Dim loc As String

filepath = ThisWorkbook.Path


FinalRow = Range("A99").End(xlUp).Row
For i = 2 To FinalRow

Set objWordApp = New Word.Application

objWordApp.Visible = False

objWordApp.Documents.Open Filename:=filepath & "\" & "BLANK.doc"

On Error GoTo 0

Set objDoc = objWordApp.Documents(1)

nimi = "Title " & Sheets("firmad").Range("A" & i)
loc = filepath & "\"
str1 = Sheets("firmad").Range("A" & i)
str2 = Sheets("firmad").Range("B" & i)

'// Goto a pre-defined bookmark and type//
objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="firma"
objWordApp.Selection.TypeText Text:=str1

objWordApp.Selection.Goto What:=wdGoToBookmark, Name:="mail"
objWordApp.Selection.TypeText Text:=str2


objDoc.ExportAsFixedFormat Outputfilename:=filepath & "\" & nimi, ExportFormat:=wdExportFormatPDF

objWordApp.Documents.Close

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = str2
        .CC = ""
        .BCC = ""
        .Subject = "hi lala"
        .Body = "Say something in email body if you want."
        .Attachments.Add koht & nimi 
        .Display
    End With
   
On Error GoTo 0

Set objDoc = Nothing
Set objWordApp = Nothing
    Set OutMail = Nothing
    Set OutApp = Nothing

Next i


End Sub

as you probably notice im not too good at it. i would also be greatful for tips abt fixing any other problems in the code

Silver

 

[shefter]

can i bump here?