VBA Code Issue

[KFerguson84]

Hello.

I am trying to get the below code to run to open an email addressed to email addresses that populate in a cell based on a VLOOKUP.

The code is

[Private Sub CommandButton21_Click()Sub SendingSheet()


Dim oMSOutlook As Object
Dim oEmail As Object


Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)


With oEmail
.To = ActiveSheet.Cells(1, 1)
.CC = ActiveSheet.Cells(2, 1)
.BCC = ActiveSheet.Cells(3, 1)
.Send
End With




Set oMSOutlook = Nothing
Set oEmail = Nothing


End Sub]

When I run it, I get an error that reads:

Compile Error:

Expected End Sub

I am good at Excel but new to Visual Basic. I'm hoping someone can tell me what I am doing to cause the error and point me in the right direction.

Thank you!!!!

 

[KFerguson84]

Here is a repost of the code. It didn't format correctly when I copied it.

Private Sub CommandButton21_Click()
Sub SendingSheet()


Dim oMSOutlook As Object
Dim oEmail As Object


Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)


With oEmail
.To = ActiveSheet.Cells(1, 1)
.CC = ActiveSheet.Cells(2, 1)
.BCC = ActiveSheet.Cells(3, 1)
.Send
End With




Set oMSOutlook = Nothing
Set oEmail = Nothing


End Sub

 

[mole999]

Private [COLOR=#ff0000]Sub[/COLOR] CommandButton21_Click()
[COLOR=#ff0000]Sub[/COLOR] SendingSheet()

did you mean

Private Sub CommandButton21_Click()
SendingSheet

 

[KFerguson84]

mole999,
I changed the code to what you posted above, now I am getting a different error that says:

Compile Error:

Sub or Function not defined

 

[par60056]

What is the line "Sub SendingSheet()"? You have a sub defined within another sub.

 

[KFerguson84]

My knowledge in VBA is about as limited as possible. I understand the functionality but I have no idea how the write the code to perform that functionality. I am looking for a code that I can attach to an ActiveX button that, when clicked, will copy an email address from one cell into the "To:" bar of an Outlook email, and copy the data from another cell into the "BCC:" bar of the same email. I would also like to pull data from a third cell into the subject line.

Can anyone assist with a code that would achieve this? I greatly appreciate the help!