Outlook VBA to open Excel attachment and send recipient's email address to a workbook cell?

Status
Not open for further replies.

Dan Smith

Member
Outlook version
Outlook 2007
Email Account
Exchange Server 2007
Hello All,

Using VBA with Outlook 2007, is it possible to open an Excel attachment from an unsent new or reply email message, pass the recipient's email address from the "To:" field to a cell in the workbook that was just opened and then close the email message?

After several months of trying to send cell data from an attached Excel 2007 workbook to the open but unsent Outlook 2007 new or reply message, I realized I may be looking at the task from the wrong direction.

Any help would be sincerely appreciates, thanks!
 

Forum Admin

Senior Member
It would be possible. This will save the attachment from the open message and open it - you'll need to reference Excel and grab the value from the desired cell.


Code:
Dim oItem As Outlook.MailItem
Dim strfile As String
Dim sText As String

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
  "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub OpenExcel()
    Dim oApp As Object, oNS As Object, oInbox As Object
    Dim oAttach As Object
    Dim FileName As String
    Dim enviro As String
   
    enviro = CStr(Environ("USERPROFILE"))
    FileName = enviro & "\Documents\"


    Set oApp = Outlook.Application
    Set oItem = oApp.ActiveInspector.CurrentItem

       If oItem.Attachments.count <> 0 Then
            For Each oAttach In oItem.Attachments
            strfile = FileName & oAttach.FileName
                oAttach.SaveAsFile strfile
                Exit For
            Next
        Else
            MsgBox "The message doesn't have an attachment"
        End If
   
 ShellExecute 0, "open", strfile, vbNullString, vbNullString, 0
 CopyFromExcel
 oItem.To = sText
 End Sub


Sub CopyFromExcel()
 Dim xlApp As Object
 Dim xlWB As Object
 Dim xlSheet As Object
               
     On Error Resume Next
     Set xlApp = GetObject(, "Excel.Application")
     If Err <> 0 Then
         Application.StatusBar = "Please wait while Excel source is opened ... "
         Set xlApp = CreateObject("Excel.Application")
         bXStarted = True
     End If
     On Error GoTo 0
     'Open the workbook to input the data
     Set xlWB = xlApp.Workbooks.Open(strfile)
     Set xlSheet = xlWB.Sheets("Sheet1")

     sText = xlSheet.Range("A1")
     xlWB.Close 1
     If bXStarted Then
         xlApp.Quit
     End If
     
     Set xlApp = Nothing
     Set xlWB = Nothing
     Set xlSheet = Nothing
 End Sub
 

Dan Smith

Member
Outlook version
Outlook 2007
Email Account
Exchange Server 2007
Thank you for your response and I sincerely appreciate you taking your time to respond to my question. Your code works perfectly for inputting cell data from the newly opened Excel document into the "To:" address filed of a new message but I am actually looking to do the reverse. Is there a way to send the recipients email address from the "To:" field to a cell in the newly opened Excel attachment? Thank you for your help!
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
Diane Poremsky Outlook VBA: Work with Open Item or Selected Item New Slipstick.com Articles 0
C Open/Close Outlook - Via Excel VBA Using Outlook 2
M Outlook 2k7 - open a mdb in vba Using Outlook 1
O determine if Outlook is open in VBA Using Outlook 1
W Using Excel UserForm from Open Workbook in Outlook VBA Outlook VBA and Custom Forms 5
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
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
M VBA to change flag status in outlook contact item Outlook VBA and Custom Forms 3
T VBA outlook, detect priority emails Outlook VBA and Custom Forms 5
C Need VBA code to automatically save message outside outlook and add date Outlook VBA and Custom Forms 1
stephen li VBA Outlook send mail automatically by specified outlook mail box Outlook VBA and Custom Forms 1
S Outlook VBA Contacts Notes Outlook VBA and Custom Forms 0
O VBA to Run Font Change on Outlook Startup Outlook VBA and Custom Forms 4
P Outlook 2007 Email Categorization using VBA Outlook VBA and Custom Forms 1
O VBA or other solution for Outlook tasks to OneNote Outlook VBA and Custom Forms 0
S Automatically selecting folders and deleting messages in Outlook VBA Outlook VBA and Custom Forms 7
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
Diane Poremsky Outlook VBA: Use a Text File to Populate a ListBox New Slipstick.com Articles 0
C Saving Outlook attachments and links to attachments with VBA Outlook VBA and Custom Forms 2
Diane Poremsky Working with VBA and non-default Outlook Folders New Slipstick.com Articles 0
C Outlook VBA to set current account Outlook VBA and Custom Forms 1
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender New Slipstick.com Articles 0
F VBA routine to write new sub routine in outlook Outlook VBA and Custom Forms 0
D Change sender name outlook vba 2010 Custom Userform Outlook VBA and Custom Forms 1
G Adding a contact to Outlook with a custom form using Access VBA Outlook VBA and Custom Forms 1
C Outlook 2016 - converting Word VBA to default Outlook message Outlook VBA and Custom Forms 0
N VBA Script to Send Automatic Emails from Outlook 2010 Outlook VBA and Custom Forms 1
P Outlook 2016 from Excel 2016 VBA Using Outlook 1
Similar threads


















































Top