VBA Outlook reference to Excel and assigning category

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
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 the great help from Diane Poremsku for which I am very greatful, but now I am stuck with two things:

1. 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. I believe I am not referencing properly

2. 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”

Here is the code:

Sub Attach_your_last_saved_file()
' create 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

Can you please help with any of these? Any help will be much appreciated! There is one specific that I use two different Outlook accounts and this is not the default one.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
On a first glance the txt variable should have the address, and assigning the category seems to be correct, too. However, without some indentation the code is hardly to get quickly. Use the code tags to paste the code properly.
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
I am sorry, I did not know how to paste it properly. Here it is

Code:
Sub Attach_your_last_saved_file()
'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\outlook_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
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
txt is added to the To property of the reply item, the category is added to the original item, that is the one you're replying to. If you miss the address, check treshold_file.Worksheets(4).Range("C6"). Probably the cell is empty.
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
Code:
Dim obj As New DataObject
Here I get compile error: "user-defined type not defined."...

when I remove the "obj", I get runtime error 424: object required..
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Errors have a meaning, you can speed things up here if you mention them upfront.

The first error means you didn't set a reference to the MSForms library. This line
Code:
obj.SetText txt
adds the content of txt to the clipboard. If you really want the address there, then click Tools/References, and tick "Microsoft Forms 2.0...", so you can use the DataObject. If you don't need the address in the clipboard (at least the shown code doesn't need it) , then simply delete the declaration, and delete the obj.SetText line.
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
Yes, I removed these two lines from the code:

Code:
Dim obj As New DataObject
obj.SetText txt
When I run the code without these two lines I get: "runtime error 424: object required."

I do not want to copy anyhting to clipboard, this is not my intention, I simply want the name in the excel file (txt variable) to be pasted in "to" automatically. Can you pls help me with this?
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
The object required error is here..
Code:
Set sheet4 = treshold_file.Object.Sheets(4)
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
Ok, great! This works now when the data in C6 is saved. However, in my case the file is currently opened so if I open the file once again, I will lose the non-saved info and will not get the reference.

Treshold_file is xlsm file which is currently opened and I want the macro to get to cell C6 of the already opened workbook. Is that possible as well? Maybe this needs to be changed?:

Set Wb = Xl.Workbooks.Open(File)
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Sure, instead of calling Open check first if Item(...) returns the object.
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
Thanks, Michael! The code works beautifully now. I also found the solution how to assign the category. I only had to change it to:
Code:
.Category = "Test"
Once again thanks for your invaluable help and cheers!
 

Nravota

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server 2010
I am sorry, the correct one is :

Code:
.Categories ="Test"
 

Similar threads

Top