Send Email Macro

[RLPeloquin]

The attached code works great but I'm having problems with this line:
strBody = "Here is a copy of your power usage: " & Str(Sheet2.Cells(2, 1))
I'd like the email to send A1:H42
Here is the complete code: Thanks in Advance!!
Sub Send_Email()

Dim CDO_Mail As Object

Dim CDO_Config As Object

Dim SMTP_Config As Variant

Dim strSubject As String

Dim strFrom As String

Dim strTo As String

Dim strCc As String

Dim strBcc As String

Dim strBody As String

strSubject = "Results from Excel Spreadsheet"

strFrom = "myemail"

strTo = "youremail"

strCc = ""

strBcc = ""

strBody = "Here is a copy of your power usage: " & Str(Sheet2.Cells(2, 1)) 'This is the line I'm having trouble with

Set CDO_Mail = CreateObject("CDO.Message")

On Error GoTo Error_Handling



Set CDO_Config = CreateObject("CDO.Configuration")

CDO_Config.Load -1



Set SMTP_Config = CDO_Config.Fields



With SMTP_Config

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "myemail"

.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

.Update

End With



With CDO_Mail

Set .Configuration = CDO_Config

End With



CDO_Mail.Subject = strSubject

CDO_Mail.From = strFrom

CDO_Mail.To = strTo

CDO_Mail.TextBody = strBody

CDO_Mail.CC = strCc

CDO_Mail.BCC = strBcc

CDO_Mail.Send



Error_Handling:

If Err.Description <> "" Then MsgBox Err.Description



End Sub

 

[GWteB]

The attached code works great but I'm having problems with this line:
strBody = "Here is a copy of your power usage: " & Str(Sheet2.Cells(2, 1))

The Str function requires a numeric value as an argument. If it's not numeric this line will error out. Perhaps a type conversion is needed:
strBody = "Here is a copy of your power usage: " & CStr(Sheet2.Cells(2, 1)) 'This is the line I'm having trouble with

 

[RLPeloquin]

I'm sorry but I'm new to VBA. I don't understand what you mean by: "The Str function requires a numeric value as an argument. If it's not numeric this line will error out. Perhaps a type conversion is needed:" It's just over my head!

 

[GWteB]

In this line you're assigning a string value to the strBody variable.
The string value consists of a string ( Here is a copy of your power usage: ) and a value (of an unknown type) pulled from Sheet2.Cells(2,1). That value of a unknown type is passed on as an argument to the Str function (in your original code). Replacing the Str function by the CStr function, which converts any type to a string, might be a solution to your issue.

 

[RLPeloquin]

Thanks, I changed the "Str" to "CStr". How do I change "Sheet2.Cells(2, 1). Does it need to be a .Range like ("A1:H42"). that still doesn't work either. Sorry, I'm really trying!

 

[GWteB]

Do I understand correctly, that you want to assign the contents of multiple cells together to one string variable?

 

[RLPeloquin]

Yes, the entire contents from A1 thru H42

 

[GWteB]

Replace this
strBody = "Here is a copy of your power usage: " & Str(Sheet2.Cells(2, 1)) 'This is the line I'm having trouble with

by this

strBody = "Here is a copy of your power usage: " & RangeConc(ActiveSheet.Range("A1:H42"))

and paste this in the same module

Public Function RangeConc(ByRef argRange As Range) As String

    Dim c       As Range
    Dim sTmp    As String
    
    If Not argRange Is Nothing Then
        For Each c In argRange
            sTmp = sTmp & c.Text & vbCrLf
        Next c
        RangeConc = sTmp
    End If

End Function

 

[RLPeloquin]

Wow! It's working! Thanks so much for your help. God bless you. I don't know if this is appropriate or not but if you could help me tweak a few things, I'd be glad to pay you. Please don't be offended it's just I think you deserve something for your expert help.

 

[GWteB]

Glad it works for you and thanks for letting me know.
Can't take advantage of your offer, see also Rule #5 of the forum rules. You are of course free to start a new thread, there are enough volunteers here on this forum with perhaps even more expertise than I have.