• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Can I updated Excel from Outlook VBA

Hi Team,

Can someone tell me how a update an excel file from Outlook VBA.
  • 6
  • 4
1 Solution
Just searching through the solutions, this seems to fit what you're looking to do.  If not, can you provide some detail?

Sandesh555Author Commented:
checking the link provided by you
Sandesh555Author Commented:
Its lot of coding to go through.

What I need is how to update a specific cell in excel.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Sandesh555Author Commented:
from outlook vba
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.
Sandesh555Author Commented:
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.

Sandesh555Author Commented:
for example I wnat to send the details like, from, to, subject etc to an excel file already saved on the local system.
Sandesh555Author Commented:
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....
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

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now