VBA - How to string two commands into one macro

Florida1510 · Mar 11, 2021

Folks,

I need some assistance I'm trying to add a second request to my existing macro to either screenshot or copy a range of cells from the ACC Data Input tab - Sheet1 (Range A1:D21). I would like for the range to be placed right under the wording "High Usage SIMs - Average SIM Usage - X.XXMB" which is part of the HTMLBody of this code. For the life of me I can't get it to work. Any help would be truly appreciated.

Sub sendEmail()

' ******************************** Variable Declaration ********************************
' Dim Req_id As Integer, nextReq_id As Integer, x As Integer, y As Integer, rownumber As Integer, _
' colnumber As Integer, finalRow As Integer, finalColumn As Integer, errCounter As Integer

' Dim TestFreq As String, month As String, pvt As PivotTable
Dim RangeToCopy As Range
Dim wsACC As Worksheet, wsContacts As Worksheet
Dim OutApp As Object, OutMail As Object
Dim lRow As Long
Dim PEmailId As String, CompanyName As String, SEmailId As String, CycleClose As String, Now As String
Dim FilesToOpen

' ******************************** Setting variables ********************************

Set wsACC = ThisWorkbook.Sheets("ACC")
Set wsSheet1 = ThisWorkbook.Sheets("ACC Data Input")
Set wsContacts = ThisWorkbook.Sheets("Contacts")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.createitem(0)

' ******************************** processing start ********************************

'Setting width for month column to avoid it being displayed as #### in outlook

CompanyName = wsContacts.Range("A2")
PEmailId = wsContacts.Range("B2")
SEmailId = wsContacts.Range("C2")
CycleClose = wsContacts.Range("D2")

With wsACC
.Activate

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Set RangeToCopy = .Range("A1:L" & lRow + 1)
Columns("A:L").AutoFit

' ********************************** Prepare the email reminder **********************************
Set OutMail = OutApp.createitem(0)
With OutMail
.Display 'or use .Send
.To = PEmailId
.CC = SEmailId
' .BCC = ""
' .attachments.Add

.Subject = CompanyName & " ESS Weekly Update " & Date
.HTMLBody = " Hello All" & "," & _
"Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". " & _
"Attached is the report for the high data usage devices for the week." & _
"The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations." & RangetoHTML(RangeToCopy) & _
" * Usage will continue to be monitored through the cycle." & _
" High Usage SIMs - Average SIM Usage - X.XXMB" & "" & " Maintenance" & "" & _
" Billing" & "" & _
" Items of Note" & "" & _
" Kindly advise if you have any questions or need further information." & OutMail.HTMLBody

End With

End With

ExitHandler:
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub

End Sub

Anthony47 · Mar 11, 2021

You already use RangetoHTML, you have just to reuse that function for the desired range...

Try replacing this line:

Rich (BB code):

"<font size=""3""> <p><b>High Usage SIMs - Average SIM Usage - X.XXMB" & "<p>" & "<font size=""3""> <p><b>Maintenance" & "<p>" & _

with

Code:

"<font size=""3""> <p><b>High Usage SIMs - Average SIM Usage - X.XXMB" & "<p>" &<br> & RangetoHTML(wsSheet1.Range("A1:D21") _
& "<font size=""3""> <p><b>Maintenance" & "<p>" & _

The macro then continues with the line " Billing" & "" & _

Bye

Florida1510 · Mar 11, 2021

Anthony,

Thank you for the quick response but I'm still getting a compliance Sytax error error on the code. Any thoughts?

.Subject = CompanyName & " ESS Weekly Update " & Date
.HTMLBody = " Hello All" & "," & _
"Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". " & _
"Attached is the report for the high data usage devices for the week." & _
"The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations." & RangetoHTML(RangeToCopy) & _
" * Usage will continue to be monitored through the cycle." & _
" High Usage SIMs - Average SIM Usage - X.XXMB" & "" & ", & RangetoHTML(wsSheet1.Range("A1:D21") _
& " Maintenance" & "" & _
" Billing" & "" & _
" Items of Note" & "" & _
" Kindly advise if you have any questions or need further information." & OutMail.HTMLBody

Anthony47 · Mar 11, 2021

Rather than using such a long multi-multi lines command you should split it in smaller chunks; for example:

VBA Code:

.HTMLBody = "<font size=""3""> <p><b>Hello All" & ",<p>"                                                              'First chunk                                    
.HTMLBody = .HTMLBody & "Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". <p>"   'Second chunk
.HTMLBody = .HTMLBody & "Attached is the report for the high data usage devices for the week.<p>"                     'Third chunk
'etc etc

This should be easier to check

Anyway, I missed a small but significant "doublequote" in my code:

Rich (BB code):

.HTMLBody = .HTMLBody & "<font size=""3""> <p><b>High Usage SIMs - Average SIM Usage - X.XXMB" & "<p>" & ",<br>" & RangetoHTML(wsSheet1.Range("A1:D21")
'other chunks

The corrected portion is the one in Red & bold

Bye

Florida1510 · Mar 11, 2021

Anthony,

Thank you again I have done everything you have recommended but on one line I'm now getting the an Syntex error on line:

.HTMLBody = .HTMLBody & " High Usage SIMs - Average SIM Usage - X.XXMB" & "" & ", " & RangetoHTML(wsSheet1.Range("A1:D21")

This is what the code now looks like:

.Subject = CompanyName & " ESS Weekly Update " & Date
.HTMLBody = " Hello All" & ","
.HTMLBody = .HTMLBody & "Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". "
.HTMLBody = .HTMLBody & "Attached is the report for the high data usage devices for the week."
.HTMLBody = .HTMLBody & "The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations." & RangetoHTML(RangeToCopy)
.HTMLBody = .HTMLBody & " * Usage will continue to be monitored through the cycle."
.HTMLBody = .HTMLBody & " High Usage SIMs - Average SIM Usage - X.XXMB" & "" & ", " & RangetoHTML(wsSheet1.Range("A1:D21")
.HTMLBody = .HTMLBody & " Maintenance" & ""
.HTMLBody = .HTMLBody & " Billing" & ""
.HTMLBody = .HTMLBody & " Items of Note" & ""
.HTMLBody = .HTMLBody & " Kindly advise if you have any questions or need further information." & OutMail.HTMLBody

End With

Any thoughts

Owen

Anthony47 · Mar 11, 2021

Grrrr...
That line need a final closing parenthesis ")": RangetoHTML(wsSheet1.Range("A1:D21"))

Anthony47 · Mar 11, 2021

I realize only now that you start with something in the HTMLBody (a signature?).
So you have to "compose" your body using a string variable, and then you compile HTMLBody:

VBA Code:

myMess = "<font size=""3""> <p><b>Hello All" & ",<p>"
myMess = myMess & "Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". <p>"
'etc
'etc
myMess = myMess & "<font size=""3""><p> Kindly advise if you have any questions or need further information.</p></font></b>"
.HTMLBody = myMess & .HTMLBody

Florida1510 · Mar 11, 2021

Anthony,

I'm still getting that same Sntax Error on line:

myMess = myMess & " High Usage SIMs - Average SIM Usage - X.XXMB" & "" & ", " & RangetoHTML(wsSheet1.Range("A1:D21")

This is what the code looks like now:

.Subject = CompanyName & " ESS Weekly Update " & Date
myMess = " Hello All" & ","
myMess = myMess & "Below is the ESS Weekly Update for the cycle closing on the " & Now & CycleClose & ". "
myMess = myMess & "Attached is the report for the high data usage devices for the week."
myMess = myMess & "The table below reflects the current cycle to date usage through " & Date & " and current rate plan allocations." & RangetoHTML(RangeToCopy)
myMess = myMess & " * Usage will continue to be monitored through the cycle."
myMess = myMess & " High Usage SIMs - Average SIM Usage - X.XXMB" & "" & ", " & RangetoHTML(wsSheet1.Range("A1:D21")
myMess = myMess & " Maintenance" & ""
myMess = myMess & " Billing" & ""
myMess = myMess & " Items of Note" & ""
myMess = myMess & " Kindly advise if you have any questions or need further information." & OutMail.HTMLBody

Anthony47 · Mar 11, 2021

Did you miss my previous message?

That line need a final closing parenthesis ")": RangetoHTML(wsSheet1.Range("A1:D21"))

Florida1510 · Mar 11, 2021

Anthony,

You're a Rock Star that worked I can't thank you enough for all your assistance. I have learned a ton tonight.

Have a wonderful evening!!!!!

Owen

VBA - How to string two commands into one macro

Florida1510

New Member

Attachments

Excel Facts

Anthony47

Well-known Member

Florida1510

New Member

Anthony47

Well-known Member

Florida1510

New Member

Anthony47

Well-known Member

Anthony47

Well-known Member

Florida1510

New Member

Anthony47

Well-known Member

Florida1510

New Member

Similar threads

Forum statistics

Share this page

VBA - How to string two commands into one macro

New Member

Attachments

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock