none
Creating an email from excel macro (But don't send) RRS feed

  • Question

  • Good Morning,

    I had an issue I was hoping someone could help with.

    I want to be able to run a Macro in excel that will open a draft email from the user's outlook that would:

    Populate the 'To:' with whatever email address are in sheet4 range a1:A5

    Subject: "Orders due today"

    and the body of the email to contain range B1:U50 from sheet 'summary'.

    But not actually send the email, I want the user to push the send button in the email. I know this probably isn't that difficult, but I am struggling to find some code that works. Do I need to activate a Reference too somehow?

    Thanks in advance!



    Wednesday, February 14, 2018 4:18 PM

All replies

  • Hi pete6256,

    You can try to refer code below.

    Sub demo()
        Dim aOutlook As Object
        Dim aEmail As Object
        Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String
        Dim str As String
        
            Set aOutlook = CreateObject("Outlook.Application")
            Set aEmail = aOutlook.CreateItem(0)
            Worksheets("Sheet4").Activate
            Set rngeAddresses = ActiveSheet.Range("A1:A5")
            
                For Each rngeCell In rngeAddresses.Cells
                    strRecipients = strRecipients & ";" & rngeCell.Value
                Next
        
                    aEmail.Importance = 2
                    
                    aEmail.Subject = "Orders due today"
                    
                    str = "Sample Body" & Sheets("summary").Range("B1:U50")
                    aEmail.Body = str
                    
                    aEmail.To = strRecipients
                    
                    aEmail.display
    End Sub

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 15, 2018 1:45 AM
    Moderator