Excel Not Attaching to Email using FieldSetText Method

[mjohnson24]

I'm getting a run time error 438 or 91 when I get to the point of attaching the excel spreadsheet. The rest of the macro works perfectly. I know it something simple. Below is the full macro.


Sub EMAILTEST()

Dim Perweek As String
Dim Pathname As String
Dim Variance As String
Dim AHR As String
Dim Region As String
Dim Zone As String

ActiveWorkbook.CheckCompatibility = False
Application.DisplayAlerts = False

ActiveWorkbook.Save
Perweek = InputBox("Enter in (PXXWkX)", "Enter in Period and Week", "PxxWkx")
Pathname = ActiveWorkbook.Path & "\2015\"


If Val(Application.Version) < 12 Then
ActiveWorkbook.SaveAs Filename:=Pathname & Perweek _
, FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
ActiveWorkbook.SaveAs Filename:=Pathname & Perweek _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

Variance = Range("G3")
AHR = Range("D3")
Region = Range("P3")
Zone = Range("Q3")


'EMAIL
Dim NSession As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim stAttachment As String
Dim AttachMe As Object
Dim EmbedObj As Object


stAttachment = Pathname

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDoc = NUIWorkSpace.ComposeDocument("", "", "Memo")
Set NUIdoc = NUIWorkSpace.CURRENTDOCUMENT



With NUIdoc
.EditMode = True
.FieldSetText "EnterSendTo", "Erica Elliott"
'.FieldSetText "EnterCopyTo", "John Ericson; Steve Pearson; Rick Olson; Michael Witynski; Zone VPs; Neil Curran; Angie Elkins"
.FieldSetText "Subject", "Mid-Week Payroll Summary - " & Perweek
.FieldSetText "Body", "Below outlines where each region has performed at the Mid-Week point for payroll: " & vbNewLine & _
" o Company Variance= " & Format(Variance, "Percent") & vbNewLine & _
" o Company AHR= " & Format(AHR, "Currency") & vbNewLine & _
" o There are " & Region & " Region(s) and " & Zone & " Zone(s) below a -1% variance." & vbNewLine & vbNewLine & vbNewLine & _
.GoToField("Body")
.Paste


ActiveWorkbook.Activate
Sheets("Summary").Select
Range("B1:D44").Select
Selection.EntireColumn.Hidden = True
Range("A1:G44").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'the cells to copy and paste
.Paste
Application.CutCopyMode = False

End With

'this is where my issue is occurring
If stAttachment <> "" Then
Set AttachMe = NUIdoc.CreateRichTextItem("stAttachment")
Set EmbedObj = AttachMe.EmbedObject(1454, "", "stAttachment", stAttachment)
NUIdoc.CreateRichTextItem ("Attachment")
End If

Set NUIdoc = Nothing
Set NDoc = Nothing
Set NUIWorkSpace = Nothing
Set NSession = Nothing
Set AttachMe = Nothing
Set EmbedObj = Nothing

ActiveWorkbook.CheckCompatibility = False
Application.DisplayAlerts = False

ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub