1

I want to export Outlook email body text to an Excel spreadsheet.

I get "Range' of object'_global' failed" on

If OutlookMail.ReceivedTime >= Range("Email_Receipt_Date").Value Then

Before running the code, I closed Outlook, I checked the categories in the Excel spreadsheet, and I selected all the correct references. From my understanding, it's not recognizing the date.

Each named range refers to a single cell in Excel.

Sub getDataFromOutlook()
Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application

Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("staffingMail")

i = 1

For Each OutlookMail In Folder.Items

    If OutlookMail.ReceivedTime >= Range("Email_Receipt_Date").Value Then
        Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_subject").Offset(i, 0).Columns.AutoFit
        Range("eMail_subject").Offset(i, 0).VerticalAlignment = xlTop
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_date").Offset(i, 0).Columns.AutoFit
        Range("eMail_date").Offset(i, 0).VerticalAlignment = xlTop
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("eMail_sender").Offset(i, 0).Columns.AutoFit
        Range("eMail_sender").Offset(i, 0).VerticalAlignment = xlTop
        Range("eMail_Body").Offset(i, 0).Value = OutlookMail.Body
        Range("eMail_Body").Offset(i, 0).Columns.AutoFit
        Range("eMail_Body").Offset(i, 0).VerticalAlignment = xlTop
        i = i + 1
    End If

Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
intothevoid
  • 11
  • 1
  • 2
    are your named ranges defined at the worksheet or at the workbook level? try `activeworkbook.range("...` or `activesheet.range("...` – nutsch Sep 13 '18 at 21:26
  • On which line does this error occur? Also, is `eMail_subject` (and the others) each a **named range,** each referring to **a *single* cell in Excel**? – ashleedawg Sep 13 '18 at 21:30
  • Excel can't figure out which Sheet you want to use. Read more here: [Range of Object Global Failed](https://stackoverflow.com/questions/12174723/run-time-error-1004-method-range-of-object-global-failed#12178460) – StoneGiant Sep 13 '18 at 22:10
  • https://stackoverflow.com/a/49548558/4539709 – 0m3r Sep 13 '18 at 22:25
  • @ashleedawg this occurs on the If OutlookMail.ReceivedTime >= Range("Email_Receipt_Date").Value Then – intothevoid Sep 14 '18 at 15:53
  • @ashleedawg yes they are each named range each referring to a single cell in excel – intothevoid Sep 14 '18 at 15:54
  • @intothevoid hello are you still stuck in this error I have the same problem and I can't solve it –  Apr 18 '19 at 07:50

0 Answers0