Compile error in email coding

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
Hi, good morning. I have the code below which is just a small part of alot of code for an email, i am trying to add the part in bold to the code infront but i keep on getting a compile error, please can you help? any help is greatly appreciated.

Code: 
"<p>" & Me.TextBox3.Value & "</p>" & "[B]<p>" & Worksheets("Northants").Range("A30:J30").End(xlUp).Value & "</p>" & _[/B]
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi i thought i would add some more infomation, with the bold code i am tring to copy the data which are in cells A30 to J30 then all they way down to the bottom of the last item added in the cell, and add this in the email below the code for TextBox3.

Hope this makes more sense? please get back to me if you require moire info :)
 
Upvote 0
I think you might be missing a continuation character but it's hard to tell from the posted code.

Can you post the rest of the code?
 
Upvote 0
hi thank you for getting back to me, this is the whole code, please see below, thank you again for your assistance :)

HTML: 
Private Sub CommandButton3_Click()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

          aEmail.HTMLBody = "<html>******>" & _
                            "<p>Hi All, Please see below todays handover." & Worksheets("Northants").Range("A1").Value & "</p>" & _
                            "<p>" & Me.TextBox7.Value & Me.TextBox9.Value & Me.TextBox8.Value & "</p>" & _
                            "<table border=""1"", cellpadding=""10"", style=background:""#a6bbde"" >" & _
                "<tr>" & _
                            "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("B8").Value & "</td>" & "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("F8").Value & "</td>" & _
                            "</tr>" & _
                "<tr>" & _
                            "<th>Timeband Slides:</th>" & "<td>" & Worksheets("Northants").Range("B9").Value & "</td>" & "<th>Timeband Extensions:</th>" & "<td>" & Worksheets("Northants").Range("F9").Value & "</td>" & _
                            "</tr>" & _
         "<tr>" & _
                            "<th>Jobs Unscheduled:</th>" & _
                            "<td>" & Worksheets("Northants").Range("B10").Value & "</td>" & _
                            "</tr>" & _
                            "</table>" & _
                "<br>" & _
                "<p>" & Me.TextBox4.Value & "</p>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p>" & Me.TextBox5.Value & "</p>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p>" & Me.TextBox6.Value & "</p>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & _
                "<p>Many Thanks</p>" & _
                "<p>Northants and Bucks Jeopardy Manager</p>" & _
                "</body></html>"


        aEmail.Recipients.Add (Worksheets("Email Links").Range("A2").Value)
        aEmail.CC = (Worksheets("Email Links").Range("B2").Value)
        aEmail.BCC = ""
        aEmail.Subject = "JM Handover for -  " & Range("E1").Value
        aEmail.Display
        
   
End Sub
 
Upvote 0
Then the below was added after the textbox3.value code
Code: 
[/B]
[B]" & Worksheets("Northants").Range("A30:J30").End(xlUp).Value & "[/B][B]

</pre>
 
Upvote 0
This should compile but I don't think it'll produce the email you want, especially since you seem to want to add multiple values from the 'Northants' sheet.


HTML: 
Option Explicit

Private Sub CommandButton3_Click()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

    Set aOutlook = CreateObject("Outlook.Application")
    Set aEmail = aOutlook.CreateItem(0)

    aEmail.HTMLBody = "<html>******>" & _
                    "<p>Hi All, Please see below todays handover." & Worksheets("Northants").Range("A1").Value & "</p>" & _
                    "<p>" & Me.TextBox7.Value & Me.TextBox9.Value & Me.TextBox8.Value & "</p>" & _
                    "<table border=""1"", cellpadding=""10"", style=background:""#a6bbde"" >" & _
                "<tr>" & _
                    "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("B8").Value & "</td>" & "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("F8").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>Timeband Slides:</th>" & "<td>" & Worksheets("Northants").Range("B9").Value & "</td>" & "<th>Timeband Extensions:</th>" & "<td>" & Worksheets("Northants").Range("F9").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>Jobs Unscheduled:</th>" & _
                    "<td>" & Worksheets("Northants").Range("B10").Value & "</td>" & _
                    "</tr>" & _
                    "</table>" & _
                "" & _
                "<p>" & Me.TextBox4.Value & "</p>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p>" & Me.TextBox5.Value & "</p>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p>" & Me.TextBox6.Value & "</p>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & Worksheets("Northants").Range("A30:J30").End(xlUp).Value & _
                "<p>Many Thanks</p>" & _
                "<p>Northants and Bucks Jeopardy Manager</p>" & _
                "</body></html>"
    
    
    aEmail.Recipients.Add (Worksheets("Email Links").Range("A2").Value)
    aEmail.CC = (Worksheets("Email Links").Range("B2").Value)
    aEmail.BCC = ""
    aEmail.Subject = "JM Handover for -  " & Range("E1").Value
    aEmail.Display
       
End Sub
 
Last edited:
Upvote 0
hi, thank you for the response back and your help, unfortunately that didnt do what i wanted like you saiid, thanks though for looking into this, the code currently below works as i want, but what i want to add is in the 'Northants' sheet i want it to add any data inputed from cells A30 to J30 and down, i thought this was the code
Code: 
& Worksheets("Northants").Range("A30:J30").End(xlUp).Value & _
, but it doesnt seem to work, or am i adding it in the wrong place? I do want the data to go after 'TextBox3' and before the 'Many Thanks' i really hope you can help me please :)

HTML: 
Private Sub CommandButton3_Click()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

          aEmail.HTMLBody = "<html>******>" & _
                            "<p>Hi All, Please see below todays handover." & Worksheets("Northants").Range("A1").Value & "</p>" & _
                            "<p>" & Me.TextBox7.Value & Me.TextBox9.Value & Me.TextBox8.Value & "</p>" & _
                            "<table border=""1"", cellpadding=""10"", style=background:""#a6bbde"" >" & _
                "<tr>" & _
                            "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("B8").Value & "</td>" & "<th>Replans:</th>" & "<td>" & Worksheets("Northants").Range("F8").Value & "</td>" & _
                            "</tr>" & _
                "<tr>" & _
                            "<th>Timeband Slides:</th>" & "<td>" & Worksheets("Northants").Range("B9").Value & "</td>" & "<th>Timeband Extensions:</th>" & "<td>" & Worksheets("Northants").Range("F9").Value & "</td>" & _
                            "</tr>" & _
         "<tr>" & _
                            "<th>Jobs Unscheduled:</th>" & _
                            "<td>" & Worksheets("Northants").Range("B10").Value & "</td>" & "<th>Special Jobs:</th>" & "<td>" & Worksheets("Northants").Range("B11").Value & "</td>" & _
                            "</tr>" & _
                            "</table>" & _
                "<br>" & _
                "<p>" & Me.TextBox4.Value & "</p>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p>" & Me.TextBox5.Value & "</p>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p>" & Me.TextBox6.Value & "</p>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & _
                "<p>Many Thanks</p>" & _
                "<p>Northants and Bucks Jeopardy Manager</p>" & _
                "</body></html>"


        aEmail.Recipients.Add (Worksheets("Email Links").Range("A2").Value)
        aEmail.CC = (Worksheets("Email Links").Range("B2").Value)
        aEmail.BCC = ""
        aEmail.Subject = "JM Handover for -  " & Range("B1").Value & "  " & Range("J1").Value
        aEmail.Display
        
   
End Sub
 
Upvote 0
How do you want the data from A30:J30 to appear in the email?
 
Upvote 0
Hi thankyou for your resonse :), i would like it to be in a table format if possible abit like when you copy and paste excel into an email. is this possible?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA Function to remove line spaces in an Email
Replies
2
Views
135
Ragham26
R
S
  • Question Question
VBA - compile error. cannot find project or library
Replies
6
Views
447
SeniorNewbie
SeniorNewbie
L
  • Question Question
Compile error: Variable not defined
Replies
3
Views
250
lharr28
L
melodramatic
  • Question Question
Compile error: Syntax error
2
Replies
12
Views
486
Artik
Artik
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
204
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top