+ Reply to Thread
Results 1 to 2 of 2

Outlook to Excel VBA Coding Error

  1. #1
    Registered User
    Join Date
    01-30-2018
    Location
    Atlanta, Georgia
    MS-Off Ver
    2013
    Posts
    3

    Outlook to Excel VBA Coding Error

    Hello,

    I am new to VBA and I'm having a hard time finding out what the issue is with my code. Everything seemed to be working fine, but now I figured out that when I'm want to export a folder from Outlook with 16 emails in it, Excel is only pulling in 15. It seems to be skipping one line of data. I've tried messing with the code and I got a "Method 'Rows' of object '_Worksheet' failed" message with this part of the code being highlighted:

    rCount = wks.Range("A1:C1" & wks.Rows.Count, 1).End(-4162).Row

    I have posted the complete code below. Thank you for any help, it will be much appreciated.

    Option Explicit
    Sub CopyToExcel()
    Dim rCount As Long
    Dim strPath As String
    Dim strSheet As String
    Dim objOL As Outlook.Application
    Dim objFolder As Outlook.MAPIFolder
    Dim objItems As Outlook.Items
    Dim obj As Object
    Dim olItem 'As Outlook.MailItem
    Dim strColA, strColB, strColC As String
    Dim nms As Outlook.NameSpace
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    strSheet = "OutlookCounterTemplate.xlsm"
    strPath = "C:\Users\Documents\"
    strSheet = strPath & strSheet
    Debug.Print strSheet
    Set nms = Application.GetNamespace("MAPI")
    Set objFolder = nms.PickFolder


    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (strSheet)
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True


    Dim rng As Range
    Set rng = wks.Range("A1:C1")
    With rng
    .Font.Bold = True
    .Font.Underline = True
    .Font.Size = 12
    .Select
    End With


    If wks.Range("A1") = "" Then
    wks.Range("A1") = "Sender Name"
    wks.Range("B1") = "To"
    wks.Range("C1") = "Modified"
    End If


    On Error Resume Next
    rCount = wks.Range("A1:C1" & wks.Rows.Count).End(-4162).Row


    Set objOL = Outlook.Application
    Set objFolder = objOL.PickFolder.Selection
    Set objItems = objFolder.Items
    For Each obj In objItems
    Set olItem = obj


    strColA = olItem.SenderName
    strColB = olItem.To
    strColC = olItem.LastModificationTime


    wks.Range("A" & rCount).Offset(1) = strColA
    wks.Range("B" & rCount).Offset(1) = strColB
    wks.Range("C" & rCount).Offset(1) = strColC


    rCount = rCount + 1

    Next

    wks.Rows.WrapText = False

    End Sub

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Outlook to Excel VBA Coding Error

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help! Automated Email coding for Excel 2016 and Outlook 2016 problems
    By lisa6421 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2017, 08:07 AM
  2. [SOLVED] Excel VBA Form Coding Error
    By kencoburn in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 04-26-2017, 12:13 PM
  3. [SOLVED] Bulk emails send use outlook & excel coding macro
    By sonu_kumar444 in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 12-26-2012, 08:06 AM
  4. VBA coding for sending email from excel with the help of outlook
    By NASIR KHAN in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2012, 07:17 PM
  5. Coding with Outlook Web App
    By JHCross in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2011, 08:45 AM
  6. [SOLVED] Excel Coding Error!
    By Killer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-23-2006, 08:55 PM
  7. Outlook & SubFolders coding
    By MSweetG222 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2006, 08:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1