Can I updated Excel from Outlook VBA

Posted on 2011-05-04
Last Modified: 2012-05-11
Hi Team,

Can someone tell me how a update an excel file from Outlook VBA.
Question by:Sandesh555
    LVL 12

    Expert Comment

    Just searching through the solutions, this seems to fit what you're looking to do.  If not, can you provide some detail?

    Author Comment

    checking the link provided by you

    Author Comment

    Its lot of coding to go through.

    What I need is how to update a specific cell in excel.

    Author Comment

    from outlook vba
    LVL 12

    Expert Comment

    Are you proficient with vba?  What data do you want to send to excel (the body, the subject, the sender)?  Do you want to learn to do this or are you wanting someone to do it for you?

    I'm just not sure what you're looking for at this point.  No one can help you unless you provide lots of detail.

    Author Comment

    I am proficient with VBA.  I want to send the details of all the emails in my inbox to an excel file.  I know how to do the same to an access file.  But not sure how to send all this details to an excel file.


    Author Comment

    for example I wnat to send the details like, from, to, subject etc to an excel file already saved on the local system.

    Author Comment

    Sample code will do the trick.. I just need a starting point, rest I can learn.  I just what to understand the method used to connect Outlook with excel files.....  The way we can generate emails from excel....

    My coding will be in Outlook.. and the Outlook VBA should be able to send some data to Excel file saved on local drive....
    LVL 12

    Accepted Solution

    I found this code from another thread.  I have copied it here for your convenience, but it was originally written by Helen Feddema.  You should be able to adapt this for your purposes.

    You'll need to add a reference to Microsoft Excel 12.0 Object Library.

    Good luck,

    Option Explicit
    Function SearchForAttachWords(ByVal s As String) As Boolean
        Dim v As Variant
        For Each v In Array("attach", "enclos")
            If InStr(1, s, v, vbTextCompare) <> 0 Then
                SearchForAttachWords = True
                Exit Function
            End If
    End Function
    Function UserWantsToAttach() As Boolean
        If MsgBox("It appears you may have forgotten to specify an attachment." _
                & vbCrLf & vbCrLf & _
                "Would you like to do this now?", _
                vbQuestion + vbYesNo) _
                = vbYes Then
            UserWantsToAttach = True
        End If
    End Function
    Sub ExecuteInsertFileCommand()
    End Sub
    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    ' Blank subject line
       Dim strSubject As String
       Dim strPrompt As String
       strSubject = Item.Subject
       If Len(Trim(strSubject)) = 0 Then
          strPrompt = "The subject line is empty. Do you want to send the message anyway?"
          If MsgBox(strPrompt, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check for Subject") = vbNo Then
            Cancel = True
          End If
       End If
    ' Alert user if no attachment
      ' I could never get this to work fully -- SDW 08/27/2008
        If Item.Class <> olMail Then Exit Sub
        If Item.Attachments.Count > 0 Then Exit Sub
        If Not SearchForAttachWords(Item.Subject & ":" & Item.Body) Then Exit Sub
        If UserWantsToAttach Then
          Cancel = True
        End If
    End Sub
    Public Sub ImportAllMailMessagesV2()
    'Created by Helen Feddema 4-Oct-2009
    'Last modified by Helen Feddema 4-Oct-2009
    On Error GoTo ErrorHandler
       Dim appExcel As Excel.Application
       Dim fldMail As Outlook.Folder
       Dim ins As Outlook.Inspector
       'itm declared as object because a folder can contain different
       'types of objects
       Dim itm As Object
       Dim lngCount As Long
       Dim lngLastRow As Long
       Dim msg As Outlook.MailItem
       Dim nms As Outlook.NameSpace
       Dim rng As Excel.Range
       Dim strFinalSaveName As String
       Dim strMessage As String
       Dim strPrompt As String
       Dim strRange As String
       Dim strSaveName As String
       Dim strWorkbookName As String
       Dim strWorkbookNameAndPath As String
       Dim strTemplatesPath As String
       Dim strTitle As String
       Dim wkb As Excel.Workbook
       Dim wks As Excel.Worksheet
       'Determine whether Outlook is running
       Set appExcel = GetObject(, "Excel.Application")
       Set nms = Application.GetNamespace("MAPI")
       Set fldMail = nms.PickFolder
       If fldMail Is Nothing Then
          MsgBox "Please select a Mail folder"
          GoTo SelectFolder
       End If
       Debug.Print "Default item type: " & fldMail.DefaultItemType
       If fldMail.DefaultItemType <> olMailItem Then
          MsgBox "Please select a Mail folder"
          GoTo SelectFolder
       End If
       'Open workbook
       strTemplatesPath = "C:\temp\"
       strWorkbookName = "Example.xlsm"
       strWorkbookNameAndPath = strTemplatesPath & strWorkbookName
       Set wkb = appExcel.Workbooks.Open(strWorkbookNameAndPath)
       appExcel.Visible = True
       'Determine starting row
       Set wks = wkb.Sheets(1)
       lngLastRow = wks.UsedRange.Rows.Count
       strRange = "A" & CStr(lngLastRow + 1)
       Debug.Print strRange
       Set rng = wks.Range(strRange)
       lngCount = 1
       'Set up loop to process all items in Inbox
       For Each itm In fldMail.Items
          'Determine class of current item
          If itm.Class <> olMail Then
             'Current item is not a mail item
             GoTo NextItem
          ElseIf itm.Class = olMail Then
             'Current item is a mail item; save data to worksheet
             Set msg = itm
             Debug.Print msg.Subject
             'Enter value from mail message
             strRange = "A" & CStr(lngLastRow + 1)
             Debug.Print strRange
             Set rng = wks.Range(strRange)
             rng.Value = msg.Subject
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=1)
             rng.Value = msg.CC
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=2)
             rng.Value = msg.BCC
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=3)
             rng.Value = msg.SentOn
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=4)
             rng.Value = msg.SenderName
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=5)
             rng.Value = msg.To
             Set rng = appExcel.ActiveCell.Offset(columnoffset:=6)
             rng.Value = msg.Body
         End If
          lngLastRow = lngLastRow + 1
          lngCount = lngCount + 1
       Next itm
       strTitle = "Done"
       If lngCount = 0 Then
          strPrompt = "No mail messages to import in " & fldMail.Name
          MsgBox Prompt:=strPrompt, _
             Buttons:=vbInformation + vbOKOnly, _
          wkb.Close savechanges:=xlDoNotSaveChanges
       ElseIf lngCount > 0 Then
          strPrompt = lngCount - 1 & " mail messages imported from " & fldMail.Name
          MsgBox Prompt:=strPrompt, _
             Buttons:=vbInformation + vbOKOnly, _
       End If
       Set appExcel = Nothing
       Exit Sub
       'Outlook is not running; open Outlook with CreateObject
       If Err.Number = 429 Then
          Set appExcel = CreateObject("Excel.Application")
          Resume Next
          MsgBox "Error No: " & Err.Number _
             & "; Description: " & Err.Description
          Resume ErrorHandlerExit
       End If
    End Sub

    Open in new window

    LVL 12

    Expert Comment

    Oops .. I included some other stuff as well.  You only need the ImportAllMailMessagesV2 code.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now