Save file as .msg instead of .txt

[jsmith2094]

HI,

I found some code online to save a list of email addresses as a TXT file but I want to save it as a .msg file so the mailing list can be added to outlook

I tried to change the extension as txt but when i try and open in outlook it says the file is corrupt

any ideas ?

Public Sub Add_Semicolon_To_Email_Addresses_List()
    Dim iRow As Double, iCol As Double, Ws As Worksheet
    Dim Email_List As String, eFile_Path As String, eFile
    
    'Initialize variables
    iRow = 2
    iCol = 1
    Email_List = ""
    eFile = FreeFile
    Set Ws = ThisWorkbook.Sheets("Sheet1")
    
    'Get Each Email Address and Add a Semicolon
    While Ws.Cells(iRow, iCol) <> ""
        Email_List = Email_List & Ws.Cells(iRow, iCol) & ";"
        iRow = iRow + 1
    Wend
    
    If Email_List <> "" Then
        'Trim the Last Semicolon
        Email_List = VBA.Mid(Email_List, 1, VBA.Len(Email_List) - 1)
        
        'Write Semicolon separated Email Address List to Output File
        'Create Email List from Excel
        eFile_Path = Ws.Cells(1, 3)
        If eFile_Path = "" Then eFile_Path = ThisWorkbook.Path & "\Email_Distribution_List1.msg"
        Open eFile_Path For Output As eFile
        Print #eFile, Email_List
        Close #eFile
        MsgBox "Email List Added with Semicolon in this path " & eFile_Path
        Exit Sub
    End If
    MsgBox "No Email Ids to Covert to Distribution List"
    
End Sub

 

[Martin1962]

Hi,

I'm using this solution :

Add a row on top of the tab.
Enter a description for each column. For example, if the first column contains the names of the contacts, and the second column contains the email addresses for the contacts, enter "Name" into A1 and "E-mail Addresses" into B1.
Save the excel sheet as a CSV file.

In outlook, you can use the import wizard ('File', 'Open&Export', 'Import/export', 'from other program/file', 'CSV') to dd the contect to the Address book/contacts.

regards,

Martin