Extract data from cell for Outlook macro

[Promasean]

I have a macro that fills an existing message with content based on what the attached filename is.

I need to set a variable for use in the macro equal to the contents of one cell on the attached excel workbook. The cell address does not change but the filename and worksheet name does.

I have tried several code snippits but can get nothing to work.

I am already getting the filename with the following code.

Set Outmail = Application.ActiveInspector.CurrentItem

If Outmail.Attachments.Count > 0 Then
var = Outmail.Attachments.Item(1).FileName
End If

I can get the worksheet name with no problem as it is in the filename.

Any help would be appreciated.

[dougbert]

Unless someone can offer a 'magic bullet' solution that I don't know of, I beleive the only way you can get the contents of a cell in an Excel attachment is to temporarily open the Excel attachment, reference the cell, assign it to a variable, then close Excel.

I have found a full sub you would need to modify for your purposes. The initial purpose of the code is to take action on incoming messages. It opens Excel and runs a macro on the attachment before saving it to your desired location.

Take a look at it and consider whether or not it could be modified for your purposes.

http://www.codeforexcelandoutlook.com/outlook-vba/automate-outlook/

[dougbert]

BTW: the link I provided is to a site owned by JP2112, whom you often see replying to questions on vbax. Just want to give credit where it was due! :friends:

[JP2112]

Thank you for the introduction dougbert!

If the file is an attachment I'm afraid you'll need to temporarily save it. I usually do so in the local temp folder, then use the Kill method to delete the file.

Here's one way to read from a closed workbook: http://www.exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html