Code stops at at the bolded word
Error says "Compile error, Can't find library or object"
Please help
--------------------------------------------------------------------------------------------
Dim rng As Range
Dim kiodex As Range
Dim outApp As Object
Dim OutMail As Object
Dim rootDir As String
Dim pdfDaily
Const adInteger = 3
Const adDate = 7
Const adVarChar = 200
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' Calculate
Set rng = ActiveWorkbook.Sheets("TheICE").Range("Print_Area")
Set kiodex = ActiveWorkbook.Sheets("ICE raw data_previous").Range("Print_Area")
' Retrieve latest pdf
'
' Set objects
Set rsFSO = CreateObject("ADODB.recordset")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFolderName = "M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily PDF"
Set objFolder = objFSO.GetFolder(strFolderName)
Set objFSO = Nothing
With rsFSO.Fields
.Append "Name", adVarChar, 200
.Append "DateCreated", adDate
End With
rsFSO.Open
n = 1
For Each objFile In objFolder.Files
rsFSO.AddNew
rsFSO("Name") = objFile.Name
temp = objFile.Name
temp = objFile.DateCreated
rsFSO("DateCreated") = objFile.DateCreated
rsFSO.Update
n = n + 1
Next
rsFSO.Sort = "DateCreated DESC "
pdfDaily = rsFSO(0) 'Name of latest record
' Reset objects
Set objFolder = Nothing
Set objFile = Nothing
Set rsFSO = Nothing
' Create new Outlook MailItem
'
Set objApp = CreateObject("Outlook.Application")
Set l_Msg = objApp.CreateItem(olMailItem)
Set colAttach = l_Msg.Attachments
l_Msg.Close olSave
strEntryID = l_Msg.EntryID
Set l_Msg = Nothing
Set colAttach = Nothing
Set l_Attach = Nothing
' CDO
On Error Resume Next
Set oSession = CreateObject("MAPI.Session")
oSession.Logon "", "", False, False
' Get message
Set oMsg = oSession.GetMessage(strEntryID)
Set oAttachs = oMsg.Attachments
Set oField = colFields.Add(CdoPR_ATTACH_MIME_TAG, "image/jpeg")
Set oField = colFields.Add(&H3712001E, "myident")
oMsg.Fields.Add "{0820060000000000C000000000000046}0x8514", 11, True
oMsg.Update
' Outlook MailItem
Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(strEntryID)
With l_Msg
'.To = "jeroen.demaeseneire@gdfsuezna.com"
.To = " Greig.Whitney@gdfsuezna.com; Gary.Ritter@gdfsuezna.com;"
'.CC = ""
.CC = "GSGNA-PortfolioGroup@gdfsuezna.com; Joe.DesChamps@gdfsuezna.com; Juan.Restrepo@gdfsuezna.com; Michael.Marsh@gdfsuezna.com; Elging.Aguilar@gdfsuezna.com; Guy.Braden@gdfsuezna.com; Thomas.Lockett@gdfsuezna.com; Keith.Crane@gdfsuezna.com; Casey.Keller@gdfsuezna.com; Steven.Taake@gdfsuezna.com;"
.BCC = ""
.Subject = "Indicative Basis numbers"
.HTMLBody = "<html><p>Below is how market is looking in the morning. <\p><p>Please see the table below for basis change from last business day. </p>" & _
RangetoHTML(rng) & _
"<html><p>Below is the evolvement of AGT and TETM3 basis provided by Kiodex (GSENA curve).</p>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\Chart001.jpg'>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\Chart002.jpg'>" & _
"<html><p>Below is the evolvement of AGT basis provided by Kiodex (GSENA curve).</p>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\AGT & TM3.jpg'>" & _
"<html><p>Please see the table below for difference between GSGNA commercial indication and Kiodex as of last business day.</p>" & _
RangetoHTML(kiodex) & _
"<html><p></p>"
.Attachments.Add strFolderName & "\" & pdfDaily
End With
l_Msg.Close (olSave)
l_Msg.display ' for you to check email before sending out
'l_Msg.send ' if one wants to send out automatically
' Reset objects
Set oField = Nothing
Set colFields = Nothing
Set oMsg = Nothing
oSession.Logoff
Set oSession = Nothing
Set objApp = Nothing
Set l_Msg = Nothing
Sheets("NBP Curve").Activate
End Sub
Error says "Compile error, Can't find library or object"
Please help
--------------------------------------------------------------------------------------------
Dim rng As Range
Dim kiodex As Range
Dim outApp As Object
Dim OutMail As Object
Dim rootDir As String
Dim pdfDaily
Const adInteger = 3
Const adDate = 7
Const adVarChar = 200
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' Calculate
Set rng = ActiveWorkbook.Sheets("TheICE").Range("Print_Area")
Set kiodex = ActiveWorkbook.Sheets("ICE raw data_previous").Range("Print_Area")
' Retrieve latest pdf
'
' Set objects
Set rsFSO = CreateObject("ADODB.recordset")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFolderName = "M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily PDF"
Set objFolder = objFSO.GetFolder(strFolderName)
Set objFSO = Nothing
With rsFSO.Fields
.Append "Name", adVarChar, 200
.Append "DateCreated", adDate
End With
rsFSO.Open
n = 1
For Each objFile In objFolder.Files
rsFSO.AddNew
rsFSO("Name") = objFile.Name
temp = objFile.Name
temp = objFile.DateCreated
rsFSO("DateCreated") = objFile.DateCreated
rsFSO.Update
n = n + 1
Next
rsFSO.Sort = "DateCreated DESC "
pdfDaily = rsFSO(0) 'Name of latest record
' Reset objects
Set objFolder = Nothing
Set objFile = Nothing
Set rsFSO = Nothing
' Create new Outlook MailItem
'
Set objApp = CreateObject("Outlook.Application")
Set l_Msg = objApp.CreateItem(olMailItem)
Set colAttach = l_Msg.Attachments
l_Msg.Close olSave
strEntryID = l_Msg.EntryID
Set l_Msg = Nothing
Set colAttach = Nothing
Set l_Attach = Nothing
' CDO
On Error Resume Next
Set oSession = CreateObject("MAPI.Session")
oSession.Logon "", "", False, False
' Get message
Set oMsg = oSession.GetMessage(strEntryID)
Set oAttachs = oMsg.Attachments
Set oField = colFields.Add(CdoPR_ATTACH_MIME_TAG, "image/jpeg")
Set oField = colFields.Add(&H3712001E, "myident")
oMsg.Fields.Add "{0820060000000000C000000000000046}0x8514", 11, True
oMsg.Update
' Outlook MailItem
Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(strEntryID)
With l_Msg
'.To = "jeroen.demaeseneire@gdfsuezna.com"
.To = " Greig.Whitney@gdfsuezna.com; Gary.Ritter@gdfsuezna.com;"
'.CC = ""
.CC = "GSGNA-PortfolioGroup@gdfsuezna.com; Joe.DesChamps@gdfsuezna.com; Juan.Restrepo@gdfsuezna.com; Michael.Marsh@gdfsuezna.com; Elging.Aguilar@gdfsuezna.com; Guy.Braden@gdfsuezna.com; Thomas.Lockett@gdfsuezna.com; Keith.Crane@gdfsuezna.com; Casey.Keller@gdfsuezna.com; Steven.Taake@gdfsuezna.com;"
.BCC = ""
.Subject = "Indicative Basis numbers"
.HTMLBody = "<html><p>Below is how market is looking in the morning. <\p><p>Please see the table below for basis change from last business day. </p>" & _
RangetoHTML(rng) & _
"<html><p>Below is the evolvement of AGT and TETM3 basis provided by Kiodex (GSENA curve).</p>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\Chart001.jpg'>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\Chart002.jpg'>" & _
"<html><p>Below is the evolvement of AGT basis provided by Kiodex (GSENA curve).</p>" & _
"<img src='M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Daily Graphs\AGT & TM3.jpg'>" & _
"<html><p>Please see the table below for difference between GSGNA commercial indication and Kiodex as of last business day.</p>" & _
RangetoHTML(kiodex) & _
"<html><p></p>"
.Attachments.Add strFolderName & "\" & pdfDaily
End With
l_Msg.Close (olSave)
l_Msg.display ' for you to check email before sending out
'l_Msg.send ' if one wants to send out automatically
' Reset objects
Set oField = Nothing
Set colFields = Nothing
Set oMsg = Nothing
oSession.Logoff
Set oSession = Nothing
Set objApp = Nothing
Set l_Msg = Nothing
Sheets("NBP Curve").Activate
End Sub