Change My E Mail From Outlook Back to Hotmail
Exporting e-mail messages from Outlook to Excel is easier than you might think. This sample VBA code automates the process, allowing you to specify exactly which fields to copy.
Office makes collaborating between applications easy, which is good news when you have data in one application but you need it in another. For instance, you might want to export a list of e-mail messages about a specific project, or from a specific person, to Excel for quick sorting, formatting, or more likely, to share with a non-Office application. The good news is that the process is simple to automate -- a bit of VBA code and a way to execute it gets the job done. (This article's sample code was written for Office 2003, but it should work as is or with minor adjustments in Office 2000 and 2002.)
Note: This information is also available as a PDF download, along with a BAS file containing the sample code.
Adding the export code
Working from Outlook, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu and enter the VBA code shown in Listing A. Next, reference the Excel object library. To do so, choose References from the Tools menu (while still in the VBE) and check Microsoft Excel 11.0 Object Library, as shown in Figure A. Then, click OK to return to the VBE.
Figure A: Update the library references to include Excel's object library.
Listing A: ExportToExcel()
Sub ExportToExcel()
On Error GoTo ErrHandler
Dim appExcel As Excel.Application Dim wkb As Excel.WorkbookDim 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
strSheet = "OutlookItems.xls" strPath = "C:Examples\"strSheet = strPath & strSheet
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 ThenMsgBox "There are no mail messages to export", vbOKOnly, _
"Error"
Exit Sub
ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "There are no mail messages to export", vbOKOnly, _
"Error"
Exit Sub
ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export", vbOKOnly, _
"Error"
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 = True 'Copy field items in mail folder.
For Each itm In fld.ItemsintColumnCounter = 1
Set msg = itm
intRowCounter = intRowCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
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.Subject
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SentOn
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime
Next itm
Set appExcel = Nothing Set wkb = NothingSet wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
Exit Sub
ErrHandler: If Err.Number = 1004 ThenMsgBox strSheet & " doesn't exist", vbOKOnly, _
"Error"
Else
MsgBox Err.Number & "; Description: ", vbOKOnly, _
"Error"
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
Using the code to export
To execute the code, position the cursor inside the procedure and press F5. Instruct your users to choose Macro from the Tools menu (inside Outlook), select Macros, select ExportToExcel in the resulting dialog box, and click Run. If they use this technique often, consider adding a button to the toolbar.
After declaring a few variables, the code displays the Select Folder dialog shown in Figure B.
Figure B: Select the folder that contains the messages you want to export.
Select a folder and click OK. Next, the code handles the following potential errors:
- The user clicks Cancel to close the Select Folder dialog box.
- The user selects a non-mail folder
- The mail folder contains no mail items
Then, the code identifies and opens an Excel workbook. In this sample code, the workbook must exist. You should update this code to accommodate your system and Excel workbook. This is also a good spot for further automating the technique by allowing users to select an existing workbook or to create a new one. For our purposes, hard coding the workbook simplifies the process.
The For Each loop is the heart of this exporting technique. There are two counters, intRowCounter and intColumnCounter. As the code inserts field values from the current message, the code updates intColumnCounter. Once the code has inserted all of the current message items, it updates intRowCounter. Without these counters, the code would write over each value in A1.
The code now inserts the first field item in A1. You can offset that by specifying a starting value for one or both counters to allow for headers or to append records instead of writing over existing values. In addition, this sample code copies only a few fields: To, SenderEmailAddress, Subject, SentOn, and ReceivedTime, as shown in Figure C.
Figure C: The macro has copied the items in the specified folder to Excel.
You can add as many fields as you need. Just be sure to include a column update statement for each field you want to copy. For instance, if you want to export the actual message text, you can add the following code:
Set rng = wks.Cells(intRowCounter, intColumnCounter) rng.Value = msg.Body intColumnCounter = intColumnCounter + 1
Note that this process doesn't transfer all the characters perfectly. You might see a few phantom characters in your Excel workbook. Also, keep in mind that the code doesn't do any formatting -- you'll have to adjust column widths and so on manually or add code to take care of the task.
If you notice that a specific field generates an error if the field is empty, use an If statement in the form:
If msg.field <> "" Then rng.Value = msg.field
to handle that error. None of the fields in the sample code poses a problem if the field is empty. The code simply leaves the appropriate cell in Excel blank.
After the Next statement and before setting all the object variables to Nothing, you might want to add code that handles the open Excel workbook in some way. For instance, you might save it and then close it.
The error handling routine is generic and simple. Be sure to test this code thoroughly and enhance it accordingly.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.
Change My E Mail From Outlook Back to Hotmail
Source: https://www.techrepublic.com/blog/microsoft-office/quickly-export-outlook-e-mail-items-to-excel/
0 Response to "Change My E Mail From Outlook Back to Hotmail"
Post a Comment