Badan
07-08-2015, 12:52 AM
Hi Guys and thanks in advance for any info shared.
I am trying to automate outlook so it can reply to a selected mail and attach the last saved excel file. I did that with help from another forum but now I am stuck with two things:
1. I am trying to assign an existing color category “Test” for the e-mails which the macro sends but it gives error 438 "Object doesn't support this property or method”
2. I want to add a reference to open workbook in excel, where I can get the correct email address. I can choose the correct one with vlookup formula, located in sheet 4, cell C6 of the excel file but do not know how to paste it automatically in “to” of the outlook mail.
Here is the code
Sub Attach_your_last_saved_file()
' You need to set a reference to the scripting object
Dim fso As Scripting.FileSystemObject
Dim strFile As String
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder
Dim dtNew As Date, sNew As String
Dim oReply As Outlook.MailItem
Dim oItem As Object
Dim signature As String
Dim objNameSpace As NameSpace
Set objNameSpace = Application.GetNamespace("MAPI")
Set fso = New Scripting.FileSystemObject
strFile = "C:\Users\bgyona02\Desktop\outolook_files\"
Set fsoFldr = fso.GetFolder(strFile)
For Each fsoFile In fsoFldr.Files
' check the extension and age
If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 5) = ".xlsx" Then
sNew = fsoFile.Path
dtNew = fsoFile.DateLastModified
Debug.Print sNew & amp; dtNew
End If
Next fsoFile
' Create a reference with the excel file
Dim treshold_fileApp As Excel.Application
treshold_file = "C:\Users\bgyona02\Desktop\treshold_file\"
Dim sheet4 As Excel.Worksheet
Set sheet4 = treshold_file.Object.Sheets(4)
Dim obj As New DataObject
Dim txt As String
txt = treshold_file.Worksheets(4).Range("C6")
obj.SetText txt
'Create e-mail item
Dim rngTo As Range
Set objApp = Application
Set oItem = objApp.ActiveExplorer.Selection.Item(1)
If Not oItem Is Nothing Then
Set oReply = oItem.Reply
End If
signature = oReply.HTMLBody
oReply.HTMLBody = signature
With oReply
.BodyFormat = olFormatHTML
.HTMLBody = "<p>First line here<p>Second line here<p/>Third line here.<p>" & "<br />" & signature
.Attachments.Add sNew
'add correct e-mail address based on the excel file
.To = txt
.Display
End With
'assign category
With oItem
.Category = "Test"
.FlagStatus = olFlagComplete
oItem.Save
End With
End Sub
There is one specific that I have two different Outlook mailboxes and the macro is for the non-default one.
Can you please help me with any of these?
I am trying to automate outlook so it can reply to a selected mail and attach the last saved excel file. I did that with help from another forum but now I am stuck with two things:
1. I am trying to assign an existing color category “Test” for the e-mails which the macro sends but it gives error 438 "Object doesn't support this property or method”
2. I want to add a reference to open workbook in excel, where I can get the correct email address. I can choose the correct one with vlookup formula, located in sheet 4, cell C6 of the excel file but do not know how to paste it automatically in “to” of the outlook mail.
Here is the code
Sub Attach_your_last_saved_file()
' You need to set a reference to the scripting object
Dim fso As Scripting.FileSystemObject
Dim strFile As String
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder
Dim dtNew As Date, sNew As String
Dim oReply As Outlook.MailItem
Dim oItem As Object
Dim signature As String
Dim objNameSpace As NameSpace
Set objNameSpace = Application.GetNamespace("MAPI")
Set fso = New Scripting.FileSystemObject
strFile = "C:\Users\bgyona02\Desktop\outolook_files\"
Set fsoFldr = fso.GetFolder(strFile)
For Each fsoFile In fsoFldr.Files
' check the extension and age
If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 5) = ".xlsx" Then
sNew = fsoFile.Path
dtNew = fsoFile.DateLastModified
Debug.Print sNew & amp; dtNew
End If
Next fsoFile
' Create a reference with the excel file
Dim treshold_fileApp As Excel.Application
treshold_file = "C:\Users\bgyona02\Desktop\treshold_file\"
Dim sheet4 As Excel.Worksheet
Set sheet4 = treshold_file.Object.Sheets(4)
Dim obj As New DataObject
Dim txt As String
txt = treshold_file.Worksheets(4).Range("C6")
obj.SetText txt
'Create e-mail item
Dim rngTo As Range
Set objApp = Application
Set oItem = objApp.ActiveExplorer.Selection.Item(1)
If Not oItem Is Nothing Then
Set oReply = oItem.Reply
End If
signature = oReply.HTMLBody
oReply.HTMLBody = signature
With oReply
.BodyFormat = olFormatHTML
.HTMLBody = "<p>First line here<p>Second line here<p/>Third line here.<p>" & "<br />" & signature
.Attachments.Add sNew
'add correct e-mail address based on the excel file
.To = txt
.Display
End With
'assign category
With oItem
.Category = "Test"
.FlagStatus = olFlagComplete
oItem.Save
End With
End Sub
There is one specific that I have two different Outlook mailboxes and the macro is for the non-default one.
Can you please help me with any of these?