Auto-export mail to Excel

Status
Not open for further replies.

MattSalas

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Office 365 Exchange
Hello,

I have an excel spreadsheet with a macro to extract emails from a selected folder which works perfectly when executed from Excel. However, I'd like to build this as a rule in Outlook 2016 to export specific emails when they arrive. I've tried a few things to no avail and was hoping someone could help. The VBA code I have in my Excel sheet is below. I've commented out many of the things I don't need, but may in the future. Right now, i just need to have the sender, subject and body of the email exported into an excel sheet.

Public Sub CopyMailtoExcel()
Dim objOL As Outlook.Application
Dim objFolder As Outlook.Folder
Dim objItems As Outlook.Items
Dim olItem As Object ' MailItem
Dim strDisplayName, strAttCount, strBody, strDeleted As String
Dim strReceived As Date
Dim rCount As Long

' On Error GoTo Err_Execute
Application.ScreenUpdating = False

Sheets("Buyflow").Select
'Find the next empty line of the worksheet
rCount = Range("D" & Rows.Count).End(-4162).Row
rCount = rCount + 1

Set objOL = Outlook.Application

' copy mail to excel
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items

For Each olItem In objItems
strAttCount = ""
strBody = ""

If olItem.Attachments.Count > 0 Then strAttCount = "Yes"

'On Error Resume Next
'collect the fields
strBody = olItem.Body

strBody = Trim(strBody)
strReceived = olItem.ReceivedTime
strSender = olItem.SenderName

'write them in the excel sheet
'Range("A" & rCount) = strReceived ' format using short date
Range("B" & rCount) = strSender
Range("C" & rCount) = olItem.Subject
Range("D" & rCount) = strBody
'Range("E" & rCount) = strReceived 'format using time
'Range("F" & rCount) = strAttCount
'Range("G" & rCount) = olItem.To
'Range("H" & rCount) = olItem.CC
'Range("I" & rCount) = olItem.BCC


'Next row
rCount = rCount + 1
Next


' Basic Formatting
Columns("B:I").Select
With Selection
.WrapText = False
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.Columns.AutoFit
End With
'Columns("E:E").Select ' body column
'With Selection
' .ColumnWidth = 150
' .Rows.AutoFit
'End With

'Range("A1:I1").Select
' With Selection
' .VerticalAlignment = xlBottom
' .WrapText = False
' .RowHeight = 55
'End With


' Date and Time
'Columns("A:A").Select
'Selection.NumberFormat = "[$-409]ddd mm/dd/yy;@"
'Range("B:B").Select
'Selection.NumberFormat = "[$-F400]h:mm AM/PM"

'Range("D:D").Select
'Selection.ColumnWidth = 20



Range("A2").Select
Sheets("Buyflow Order import").Select
Range("A2").Select

Application.ScreenUpdating = True
Set olItem = Nothing
Set objFolder = Nothing
Set objOL = Nothing
Set Reg1 = Nothing

MsgBox "Email import complete"

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub
 

pedro6655

New Member
Outlook version
Outlook 2010 64 bit
Email Account
POP3
My problem is very similar to you. I can't export my excel data to Zoho mails. My Zoho mail is linked with my yahoo mail. I thought maybe the issue was arising because of yahoo mail so I contact with Yahoo Support but they can't solve my issue as the problem is not from their end.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Matt, look at the code for all objects that belong to Excel and not to Outlook like Application or Sheets. For instance, when running in Outlook, Application points to Outlook instead of Excel. And Outlook´s Application object doesn´t know a ScreenUpdating property. You need to use variables to tell Outlook that you want to access the Excel Application object.
Here´s a sample for how to open and ref an Excel worksheet:
Open Excel File from within Outlook - VBOffice
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
R Auto display of new email does not work on non-default account Outlook VBA and Custom Forms 0
B Outlook 2016 Auto-archive creates new folder Using Outlook 4
J Edit auto-complete list in Outlook 2016+/365? Using Outlook 0
P Auto assign shared mailbox Outlook VBA and Custom Forms 1
M Outlook 2010 Problem with OutLook 2010 32 bit, after Windows Auto Update Using Outlook 3
P [SOLVED] Auto remove [EXTERNAL] from subject Using Outlook 10
Z Add text to auto-forwarded e-mail Outlook VBA and Custom Forms 4
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
Q Prompt button to auto turn on Out of Office Outlook VBA and Custom Forms 3
P Auto Insert Current Date or Time into Email Subject Outlook VBA and Custom Forms 2
S Messages moved / deleted by auto-archive are not synchronized to exchange Exchange Server Administration 8
B Outlook 2010 is Auto Purging when not configured for that Using Outlook 1
M VBA to auto forward message with new subject and body text Outlook VBA and Custom Forms 8
A Auto Accept Meetings from the General Calendar Using Outlook 3
R auto send email when meeting closes from a shared calendar only Outlook VBA and Custom Forms 2
S auto-mapping mailboxes in outlook impacting an ost file? Exchange Server Administration 2
M Auto expand Distribution List Before Sending Email Outlook VBA and Custom Forms 1
Ms_Cynic Auto-pasting email content in calendar appt? Using Outlook 2
R How Do I insert images in and Auto Reply Using Outlook 3
S Received mail as part of DL, need to auto-CC the same when replying Outlook VBA and Custom Forms 5
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 10
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 0
P Auto scroll to specific folder in Folder Pane Outlook VBA and Custom Forms 3
C Auto categorize duplicate subjects Outlook VBA and Custom Forms 11
N Auto-complete - block select emails Using Outlook 3
C Auto save outlook attachments when email is received Outlook VBA and Custom Forms 1
J HELP- Rule to auto strip prepend from external emails Using Outlook 0
S BCM Auto Backup Data and Customizations BCM (Business Contact Manager) 6
G Auto accept meeting request for non primary account Outlook VBA and Custom Forms 1
J Outlook Rules - Changing auto-submit address in multiple rules, according to rule name Outlook VBA and Custom Forms 0
E Outlook Form - Voting Responses Not Auto Processing If Form Contains Any Code Outlook VBA and Custom Forms 0
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
K Extract email address from body and auto-reply outlook Using Outlook 1
S Auto move search results to folder Outlook VBA and Custom Forms 0
E Outlook 2010 disable date auto-complete Using Outlook 2
C Auto subject,name,email,deferred Using Outlook 2
ashcosta2 Auto Reply rule based on speficied time Outlook VBA and Custom Forms 0
B Auto Preview Attachment in Inspector Reading Pane Outlook VBA and Custom Forms 1
Z Auto Forward Using Outlook 4
M can anyone recommend an alternative to DS auto-followup? Using Outlook 2
S Rules to auto redirect Using Outlook 5
B Auto Save of Attachments from Multiple Emails and forward attachments to user group Outlook VBA and Custom Forms 1
A Auto Insert of filename when selecting 'Remove Attachment' Using Outlook 1
C Auto Add a PDF to an outgoing Email, based on a rule Using Outlook 2
B Auto open PDF Outlook VBA and Custom Forms 0
P Is it possible to convert address book to "Auto-Complete List" (NK2)? Using Outlook 5
B Auto name checking upon Tab or Click off To field(s) Using Outlook 19
J Auto Accept Notifications from a shared icloud calendar? Using Outlook 4
B Auto scan/send Outlook VBA and Custom Forms 5
N Auto-forwarding an Email received from a specific Email address After slight modifications Using Outlook 4
Similar threads


















































Top