danny8890

New Member
Joined
Feb 7, 2018
Messages
46
Hi,

I need to get the below to start from A2 rather than A1. I think i need to Offset the row but for the life of me cannot get this to work. copied the part of the VBA where i believe this needs to be added.

If someone could help that would be great

Code:
et wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate 'this is what i've tried'


appExcel.Application.Visible = False
  'Copy field items in mail folder.
  
For Each itm In fld.Items
intColumnCounter = 1




Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Sender
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderEmailAddress
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Body
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime




Next itm


 With wkb.Sheets(1)
    .Range("A:F").WrapText = False
    
End With


     wkb.Close 1
     If bXStarted Then
         xlApp.Quit
     End If


    ' Show summary message
        MsgBox "Finished" _
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Don't use "ActiveCell" in your coding, like this:
Code:
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate 'this is what i've tried'
Rather, use a specific (or dynamic) range reference, i.e.
Code:
Range("A2")...

Also, you seldom need to select ranges in VBA to work with them.
 
Upvote 0
Thanks for your reply Jo,

So i've tried using range already but cannot see to get it working in this code. It works as its own sub. Could you help me getting into the code below

This works
Code:
Range("A1").Offset(RowOffSet:=1, ColumnOffset:=0).Select

Need to work in here
Code:
Sub ExportToExcel23()  On Error GoTo ErrHandler


Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder


Dim itm As Object
    strPath = "C:\Users\dr0808a\Desktop\UTA - Raw - Test.xlsm"
strSheet = strPath


Debug.Print strSheet
  'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
  'Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then


MsgBox "There are no mail messages to export"
Exit Sub




ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "There are no mail messages to export"
Exit Sub




ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export"
Exit Sub




End If
  'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate


appExcel.Application.Visible = False
  'Copy field items in mail folder.
For Each itm In fld.Items
intColumnCounter = 1




Set msg = itm
intRowCounter = intRowCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Sender
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderEmailAddress
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Body
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime




Next itm


  With wkb.Sheets(1)
    .Range("A:F").WrapText = False
    
End With


     wkb.Close 1
     If bXStarted Then
         xlApp.Quit
     End If


' Show summary message
        MsgBox "Finished" _


Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing


  Exit Sub
  
ErrHandler:  If Err.Number = 1004 Then
MsgBox strSheet & " doesn't exist"




Else




MsgBox Err.Number & "; Description: "




End If




Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing


End Sub
 
Upvote 0
I would need to know a bit more about your project, namely:
- the structure of your data
- a clear, detailed explanation of exactly what it is you are trying to do
 
Upvote 0
I would need to know a bit more about your project, namely:
- the structure of your data
- a clear, detailed explanation of exactly what it is you are trying to do
Hi Joe, So this code is currently sat in outlook VBA and copys all emails from what folder i select into excel. This currently copy everything across fine but starts in A1. I need this to start in row A2.
 
Upvote 0
If I understand it correctly, it looks like you just need to initialize the value of intRowCounter before you For ... Next loop.
Currently, you are not setting it to anything, so it is zero initially. You are adding one before using it, so it starts at 1.
If you put a line like the following before the "For ..." line, I think it will do what you want.
Code:
intRowCounter = 1
 
Upvote 0
Exactly what i tried already, this works however only then copys one email doesnt loop and get all the emails, i added this straighter under the intcoloumncounter
 
Upvote 0
i added this straighter under the intcoloumncounter
That's the wrong place to put it. That is inside the loop, so it will reset in every loop, so EVERY record would be placed on row 2, so each successive loop would just overwrite the previous one, so you are just left with the last one.

You need to do what I suggested, and put it before the loop.
 
Last edited:
Upvote 0
That's the wrong place to put it. That is inside the loop, so it will reset in every loop, so EVERY record would be placed on row 2, so each successive loop would just overwrite the previous one, so you are just left with the last one.

You need to do what I suggested, and put it before the loop.
Amazing!, didn't read what you put properly works now... can't believe I was two lines off getting this to work...
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top