Excel ranges don't save as strings

[jackdandcoke]

I'm getting a data type mismatch error for the following code. Any ideas?


Dim TopStore As String
Dim DMHour As String
Dim DMCumu As String

TopStore = Sheets("DM Rankings").Range("G4:H5").Value
DMHour = Sheets("DM Rankings").Range("D4:E17").Value
DMCumu = Sheets("DM Rankings").Range("A4:B17").Value

When I run the macro it says "Run-Time error '13':

Type mismatch.

[georgiboy]

I would imagine its because you are using the .Value part.

[mdmackillop]

You'll need to loop through each cell

Set tmp = Range("C23:C25")
For Each t In tmp
txt = txt & t
Next
MsgBox txt

[jackdandcoke]

I don't know where to put this in my code then

Set tmp = Range("A4:H17")
For Each t In tmp
txt = txt & t
Next
MsgBox txt
maybe it would help if I put my whole code in


Sub Auto_Open()

If Environ("runme") = "YES" Then

Sheets("Mid Atlantic Lead Event Pivot").Select
Range("A7").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("DM Rankings").Select
Range("A9").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("D9").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("G9").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh


Dim TopStore As String
Dim DMHour As String
Dim DMCumu As String

TopStore = Sheets("DM Rankings").Range("G4:H5").Value
DMHour = Sheets("DM Rankings").Range("D4:E17").Value
DMCumu = Sheets("DM Rankings").Range("A4:B17").Value


Dim OL As Object
Dim EmailItem As Object
Dim Wb As Object

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Importance = olImportanceHigh
.Subject = "Mid Atlantic Lead Entry Webathon Results"
.Body = "Top Store" & vbCrLf & TopStore _
& "Hourly Rankings" & vbCrLf & DMHour _
& "Overall Rankings" & vbCrLf & DMCumu

.To = "me@mycompany.com"
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End If


End Sub

[mdmackillop]

Can you post an image of how your email text should appear?

[jackdandcoke]

Just a really simple e-mail

[mikerickson]

You might try either Dimming them as Variant or using formulas likeTopStore = Join(Sheets("DM Rankings").Range("G4:H5").Value,", ")

[jackdandcoke]

Gives me an invalid procedure or argument.

[Bob Phillips]

See this (http://www.rondebruin.nl/mail/folder3/mail4.htm) to see how to embed ranges in an email.