Create Email in Outlook10 (Need help in changing code to take email addresses from an excel file) RRS feed

  • Question

  • Dear Developers,

    I already have macro which drafts emails once I run the macro (Works perfectly fine), however I need to create more than 100 emails at a time & I am in process of replicating this code for all the emails. If you look at the below code you will see that I have manually typed the email addresses within the code

    Now the problem is, this is my weekly task and each time I get updates in so many emails so looking at the existing code I am anticipating that I need to manually add/remove/update emails in the VBA code which is not realistic. Is there any way to change the following lines in such a way that it takes email addresses from Sheet1 of the same macro file and NOT from the VBA code? Where in Sheet1 I have listed all the 100 email address from A1 to A100. 

       .To = "XXX.YYY@ZZZ.com"  ---> Can we change this to take reference from let's say A2 of Sheet1 (Which may have many email addresses separated by comma?

    If this is possible then each time I gets updates in email, I just need to change the corresponding email address in sheet1's specific cell without going in to the VBA.

    *** CODE ***

    Sub deletion_1()
        Dim OutApp As Object
        Dim OutMail As Object

        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "XXX.YYY@ZZZ.com"
            .CC = "XXX.YYY@ZZZ.com"
            .Subject = "Hello Word1"
            .Body = "Hello," & vbCrLf & "" & vbCrLf & "Please Find the attached ...." & vbCrLf & "" & vbCrLf & "Thanks and Regards"
            .Attachments.Add ActiveWorkbook.FullName ' I can change this line to take files from specific path
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing



    End Sub

    *** OVER ***

    I am really sorry for all this but I am learning VBA and not that much proficient. I hope I am clear in my requirement.

    Can anyone please help me with this?

    Eagerly waiting for your answer.




    Thursday, January 22, 2015 12:25 AM

All replies

  • Thanks resolved by myself. 

    Only one line required it's .TO=Range("A2").Value.

    Thanks everyone who has looked in to my issue.


    Thursday, January 22, 2015 2:29 PM