Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sending a certain range of a sheet via vba in outlook

0

Hi, I'm not too comfortable with VBA yet but so far my first project is going well. Only one little details is missing. So I want to send a certain range of a certain excel sheet in my excel file via outlook mail. Right now the code sends the mail right away but I need it to be previewed in a new mail window. So this is my code:

Private Sub Send_Click()
Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range
    On Error GoTo StopMacro
    'Remember the activesheet
    Set AWorksheet = ActiveSheet
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set Sendrng = Worksheets("Send.Mail").Range("A1:O38")
    With Sendrng
        ' Select the worksheet with the range you want to send
        .Parent.Select
        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell
        'Select the range you want to mail
        .Select
        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope
            With .Item
                .To = ""
                .CC = ""
                .BCC = ""
                .Subject = "Board " & VBA.Date
                .Send
            End With
        End With
        'select the original ActiveCell
        rng.Select
    End With
    'Activate the sheet that was active before you run the macro
    AWorksheet.Select
StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

When I remove the .send section the mail is not being sent at all.


Can anyone help me out and tell me how to insert the table in a new outlook window without sending it?

Answer
Discuss

Answers

0

Your entire setup is designed to send the mail without review. So, which part of the mail do you want to review? The text, the sender, the recipient, the range? Do your reviewing before the mail is prepared. Then click to send it.

As an alternative, change the method. Use Outlook (or another email client) to prepare the mail. There you can either stop the process when the mail is ready and review it using the client. Or you can let your macro put it in the outbox where it may not be sent until 15 mintues later, depending upon the client's settings.

Discuss
0

Pansen

You can modify your existing code to preview the email before sending. Just comment out or delete the .Send line (in the code extract below) and use .Display (see changes in bold below):

With .Parent.MailEnvelope
            With .Item
                .To = ""
                .CC = ""
                .BCC = ""
                .Subject = "Board " & VBA.Date
                .Display
                '.Send
            End With
        End With
Then Excel will show a sheet with an email header and you just press the "Send this Sheet" button (below the ribbon) to send it. It will appear in the Sent box of your Outlook.

As @Variatus says, it's better to have the macro prepare your email e.g. you could put email addressses in another sheet (say "EmailAdds") then use something like (changing the bits in bold):

.To = Worksheets("EmailAdds").Range("A2")
There are other methods of sending emails using VBA but that's for another day!

Hope this works for you.

Discuss

Discussion

Pansen, did that work for you?
John_Ru (rep: 6142) Feb 8, '21 at 4:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login