Skip to main content

VBA code for export all email address and mail message from outlook to excel

Sub ExportToExcel()
On Error GoTo ErrHandler
Set appExcel = CreateObject(“Excel.Application”)
Set appExcel = 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
strSheet = “OutlookItems.xls”
strPath = “C:Attendance”
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 Then
MsgBox “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.Items

intColumnCounter = 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 = 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”, 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

Split word document into multiple documents vba

Option Explicit ‘This goes in the Declarations section of your code module.
‘Hopefully it is already there because you have ticked the ‘Require Variable Declaration’
checkbox. (Tools/Options, Editor tab.)

Sub SplitIntoPages()
Dim docMultiple As Document
Dim docSingle As Document
Dim rngPage As Range
Dim iCurrentPage As Integer
Dim iPageCount As Integer
Dim strNewFileName As String

Application.ScreenUpdating = False ‘Makes the code run faster and reduces screen _
flicker a bit.
Set docMultiple = ActiveDocument ‘Work on the active document _
(the one currently containing the Selection)
Set rngPage = docMultiple.Range ‘instantiate the range object
iCurrentPage = 1
‘get the document’s page count
iPageCount = docMultiple.Content.ComputeStatistics(wdStatisticPages)
Do Until iCurrentPage > iPageCount
If iCurrentPage = iPageCount Then
rngPage.End = ActiveDocument.Range.End ‘last page (there won’t be a next page)
Else
‘Find the beginning of the next page
‘Must use the Selection object. The Range.Goto method will not work on a page
Selection.GoTo wdGoToPage, wdGoToAbsolute, iCurrentPage + 4
‘Set the end of the range to the point between the pages
rngPage.End = Selection.Start
End If
rngPage.Copy ‘copy the page into the Windows clipboard
Set docSingle = Documents.Add ‘create a new document
docSingle.Range.Paste ‘paste the clipboard contents to the new document
‘remove any manual page break to prevent a second blank
docSingle.Range.Find.Execute Findtext:=”^m”, ReplaceWith:=””
‘build a new sequentially-numbered file name based on the original multi-paged file name and path
strNewFileName = Replace(docMultiple.FullName, “.doc”, “
” & Right$(“000” & iCurrentPage, 4) & “.doc”)
docSingle.SaveAs strNewFileName ‘save the new single-paged document
iCurrentPage = iCurrentPage + 4 ‘move to the next page
docSingle.Close ‘close the new document
rngPage.Collapse wdCollapseEnd ‘go to the next page
Loop ‘go to the top of the do loop
Application.ScreenUpdating = True ‘restore the screen updating

‘Destroy the objects.
Set docMultiple = Nothing
Set docSingle = Nothing
Set rngPage = Nothing
End Sub

 

 

Create pdf file from ms word

Sub pdffilesfromword()
Application.ScreenUpdating = False
Dim DocName As String, PDFPath, Folderpath, From, Till, Message, fs
Folderpath = “C:Merged Letters In PDF File From Word”

Set fs = CreateObject(“Scripting.FileSystemObject”)
If fs.FolderExists(Folderpath) = False Then
fs.createfolder (Folderpath)
Else: End If

From = 1
Till = 5

Message = (Till – From) + 1

While From <= Till

ActiveDocument.MailMerge.DataSource.ActiveRecord = From
DocName = ActiveDocument.Fields(1).Result
‘CheckFilename for: ?ActiveDocument.Fields(1).Code
PDFPath = Folderpath & “” & DocName & “.pdf”
Massage = ((Till – From) + 1)

ActiveDocument.ExportAsFixedFormat OutputFileName:=PDFPath, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True

From = From + 1
Wend

MsgBox (Message & ” Pdf Files Saved In = ” & Folderpath)

End Sub

VBA code for move file to other folder

Sub CheckIfFileExists()

Dim LRow As Integer
Dim LPath As String
Dim LExtension As String

Dim LContinue As Boolean
Dim MPath As Integer
Dim FNAme As String

Dim sourceFile As String
Dim targetFile As String

Dim fso As Object

LContinue = True
LRow = 2
MPath = 2
LPath = “C:New Folder”
LExtension = “.pdf”

While LContinue

If Len(Range(“A” & CStr(LRow)).Value) = 0 Then
LContinue = False

Else

aa = “C:New Folder”

Set fso = CreateObject(“Scripting.FileSystemObject”)

bb = “C:New Folder”

Set fs = CreateObject(“Scripting.FileSystemObject”)

bb = bb & “” & Range(“A” & CStr(MPath)).Value
bb = bb & “.PDF”

If fs.FileExists(bb) = True Then

Range(“B” & CStr(LRow)).Value = “Yes”

FNAme = Range(“C” & CStr(MPath)).Value
aa = aa & “” & FNAme

If fs.FolderExists(aa) = False Then
fs.createfolder aa

End If

aa = aa & “”
fso.MoveFile bb, aa

End If
End If

Set fs = Nothing
Set fso = Nothing

LRow = LRow + 1
MPath = MPath + 1
Wend

End Sub

Rows and columns, and other new limits in new excel

<

p style=”text-align: justify;”>What’s new in Microsoft Office Excel 2007

To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV.

Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory.

To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.

You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.

Office Excel 2007 also supports up to 16 million colors.

Easy-to-use PivotTables

What’s new in Microsoft Office Excel 2007

In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones that aren’t always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.

And after you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.

Using Undo in PivotTables   You can now undo most actions that you take to create or rearrange a PivotTable.

Plus and minus drill-down indicators   These indicators are used to indicate whether you can expand or collapse parts of the PivotTable to see more or less information.

Sorting and filtering   Sorting is now as simple as selecting an item in the column that you want to sort and using sort buttons. You can filter data by using PivotTable filters, such as Greater than, Equals, or Contains.

Conditional formatting   You can apply conditional formatting to an Office Excel 2007 Pivot Table by cell or by intersection of cells.

PivotTable style and layout   Just like you can for Excel tables and charts, you can quickly apply a predefined or custom style to a PivotTable. And changing the layout of a PivotTable is also much easier to do in the new user interface.

PivotCharts   Like PivotTables, PivotCharts are much easier to create in the new user interface. All of the filtering improvements are also available for PivotCharts. When you create a PivotChart, specific PivotChart tools and context menus are available so that you can analyze the data in the chart. You can also change the layout, style, and format of the chart or its elements the same way that you can for a regular chart. In Office Excel 2007, the chart formatting that you apply is preserved when you make changes to the PivotChart, which is an improvement over the way it worked in earlier versions of Excel.