VBA Outlook reference to Excel and assigning category

Status
Not open for further replies.

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.
 
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.
 
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
 
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.
 
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..
 
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.
 
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?
 
The object required error is here..
Code:
Set sheet4 = treshold_file.Object.Sheets(4)
 
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)
 
Sure, instead of calling Open check first if Item(...) returns the object.
 
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!
 
I am sorry, the correct one is :

Code:
.Categories ="Test"
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
U Reference Text for VBA for outlook Outlook VBA and Custom Forms 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 2
L Fetch, edit and forward an email with VBA outlook Outlook VBA and Custom Forms 2
BartH VBA no longer working in Outlook Outlook VBA and Custom Forms 1
W Can vba(for outlook) do these 2 things or not? Outlook VBA and Custom Forms 2
richardwing Outlook 365 VBA to access "Other Actions" menu for incoming emails in outlook Outlook VBA and Custom Forms 0
J Outlook Rules VBA Run a Script - Multiple Rules Outlook VBA and Custom Forms 0
C Outlook (desktop app for Microsoft365) restarts every time I save my VBA? Using Outlook 1
E Outlook 365 Outlook/VBA Outlook VBA and Custom Forms 11
J VBA for outlook to compare and sync between calendar Outlook VBA and Custom Forms 1
E Outlook VBA change GetDefaultFolder dynamically Outlook VBA and Custom Forms 6
S vba outlook search string with special characters Outlook VBA and Custom Forms 1
U Outlook 2019 VBA run-time error 424 Outlook VBA and Custom Forms 2
G VBA to save selected Outlook msg with new name in selected network Windows folder Outlook VBA and Custom Forms 1
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
K Outlook Office 365 VBA download attachment Outlook VBA and Custom Forms 2
V vBA for searching a cell's contents in Outlook and retrieving the subject line Outlook VBA and Custom Forms 1
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
S Excel vba code to manage outlook web app Using Outlook 10
H Custom Outlook Contact Form VBA Outlook VBA and Custom Forms 1
S Problem Checking the available stores in my Inbox (Outlook VBA) Outlook VBA and Custom Forms 0
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
O VBA Outlook Message Attachment - Array Index Out of Bounds Outlook VBA and Custom Forms 0
J Want to learn VBA Macros for Outlook. What book can you recommend? Outlook VBA and Custom Forms 2
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
N How can I increase/faster outlook VBA Macro Speed ? Using Outlook 2
N Outlook Email Rule execution through shortcut keys (VBA codes) Using Outlook 1
A VBA Code in Outlook disappears after first use Outlook VBA and Custom Forms 1
dweller Outlook 2010 Rule Ignores VBA Script Outlook VBA and Custom Forms 2
G Outlook VBA and Google Calendar ("Events") Outlook VBA and Custom Forms 1
J VBA Outlook : Subject line : Cut and Paste name to heading , number to very end of the body of Email Outlook VBA and Custom Forms 1
B Advanced Search in MS Outlook by VBA and SQL Outlook VBA and Custom Forms 2
K Outlook Archive to PST Files by Date Range VBA Script? Outlook VBA and Custom Forms 1
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
S Find a cell value in excel using outlook vba Using Outlook 1
J Execute Add-In Button from VBA Outlook 2016 Outlook VBA and Custom Forms 1
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
M Slow VBA macro in Outlook Outlook VBA and Custom Forms 5
T Outlook AntiSpam with VBA Outlook VBA and Custom Forms 1
F "Move to" O365 feature to Outlook client via VBA Outlook VBA and Custom Forms 4
B query outlook using vba Outlook VBA and Custom Forms 13
J VBA to switch Outlook online/offline Outlook VBA and Custom Forms 4

Similar threads

Back
Top