Solved

Exporting from Exchange Server

Posted on 2001-07-10
4
927 Views
Last Modified: 2012-05-04
When exporting all mailboxes out of exchange to a CSV file, does anyone know if there is a way to do this with only the SMTP email addresses coming across and not the ccmail, X400 etc... I am trying to import the list and the program can not see the email addresses because it is seeing all of the different email standards.
0
Comment
Question by:dbennett
4 Comments
 
LVL 55

Expert Comment

by:andyalder
ID: 6272554
Assuming you mean exporting the directory. Export to a .CSV, edit this with notepad and remove all the data just keeping the first line which is the column titles. Remove the titles you don't want and export again to this same file. It's the same process as in http://support.microsoft.com/support/kb/articles/Q153/0/28.asp except that in this case you want to remove some the headers rather than add one.

There's a program called header.exe in resource kit which will create the initial export file for you with your chosen columns but not needed.
0
 
LVL 2

Accepted Solution

by:
NickBentley earned 50 total points
ID: 6298523
I have been doing this as a temporary measure until our two organisations are merged.  I haven't found a way yet (haven't exactly spent a 'lot' of time on this!) to get only the SMTP addresses.
The email addresses come out as one big field.  I open the csv file in Excel and I wrote a quick & dirty macro to 'parse' the email field & only extract the SMTP address.
The macro is below from Excel 2000 (should work the same in 97).  The columns may not match up as I customised the header file to export some fields we were using that weren't included in the standard header.
At the end I have to select the current region (Ctrl+*) & format as text as the macro refuses to do this (otherwise you get errors when you import).
HTH
Nick

Sub main()
'  main 'controlling' module.
'  This just calls all the other modular (!) macro components.
'  Nick Bentley 15/1/01

ChgClassToRemote
SplitAddresses
BlankColumns
changetotext

End Sub

Sub ChgClassToRemote()
'
' ChgClassToRemote Macro
' Component to select the first column (presume that we are using the custom export csv)
' It will then change all entries to Remote so that they can be imported as custom recipients
'
'  Nick Bentley 15/1/01
'
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "Remote"
End Sub

Sub BlankColumns()
'
' Select column (I) and blank it out - this is not needed or desirable
' as the SMTP addresses have been (hopefully) parsed out of this list and
' the X400 etc addresses are not required (or valid!) for importing into a
' 'Foreign' exchange organisation
'
'  Nick Bentley 15/1/01
'
    Range("I:I").Select
    Range("I1").Activate
    Selection.Delete Shift:=xlToLeft
End Sub
Sub SplitAddresses()
'
' SplitAddresses Macro
' This first selects the column containing the concatenated addresses as exported by echange.
' It should be in column I.  Next the column is split into further columns using a percent
' character as the delimiter and simultaneously moved to range aa2 (which should be blank)
' This new range is then selected and each cell tested in turn to find where the SMTP addresses
' are.  Each smtp address is then moved to column H.
' At the end the range is cleared ready for saving.
'
'  Nick Bentley 15/1/01
'
    Dim testcell, NRow, NCol, Ntrue
    Ntrue = 0
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("AA2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="%", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
    Range("aa2").Select
    Selection.CurrentRegion.Select
    For Each testcell In Selection
        NRow = testcell.Row
        NCopyTo = "H" & NRow
        If Left(testcell, 4) = "SMTP" Then
            testcell.Copy
            Range(NCopyTo).Select
            ActiveSheet.Paste
        End If
    Next
    Range("aa2").Select
    Selection.CurrentRegion.Select
    Selection.Clear
       
End Sub

' This module Selects the current working area and then selects the blank cells
' within it (SpecialCells...) this is the equivalent of the goto special command.
' Each cell is then formatted as Text (@) so that no blank fields are lost when the
' save_as happens - doesn't seem to be 100% effective at this, so a manual select &
' format may be required.
'
'  Nick Bentley 15/1/01
'

Sub changetotext()
    Range("A1").Select
    With Selection
        .CurrentRegion.Select
        .SpecialCells(xlCellTypeBlanks).Select
        .NumberFormat = "@"
    End With
End Sub
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7270430
Hello dbennett@devx

this question is open for more then 2 months
time to clean up
if not stated otherwise

my recom will be
-PAQ
-points to NickBentley
-this will be finalized by an EE Moderator
-with no further update (15.09.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
posted by ToolzEE v1.0
0
 

Expert Comment

by:Jgould
ID: 7297070
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

Join & Write a Comment

We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
In this video we show how to create a User Mailbox in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >> Mailb…
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now