Quick macro question.

[ckporte]

I need to add a comma and a spaceband between each email address but I get an error if I do the following....

Email = Cells(ThisRow, 9) & ", " & Cells(ThisRow, 11)

It seems to have a problem if I end with a spaceband (ie. ", ").
I can use just a comma (ie. ",")
or ..a comma space comma (ie. ", ,")

...but not .... ", "

Could I have a preference shut off? Or is there a hardcode to describe a spaceband I should be using?

Any ideas?
CKPORTE

 

[bensonsearch]

Hi,
bit backward but try

Email = Cells(ThisRow, 9) & "," & " " & Cells(ThisRow, 11)

 

[ckporte]

Thank you! That did it!

Ok.. one more question ..if I may?
I would like to put text before each cell's info as a label. (ie. Item: 250) but its not working for me.

Here is my original code (without the text).

Msg = Msg & Cells(ThisRow, 2) & vbCrLf & Cells(ThisRow, 1) & _
vbCrLf & Cells(ThisRow, 9) & vbCrLf & _
Cells(ThisRow, 6) & vbCrLf & Cells(ThisRow, 9)

I started out trying to put it before Cells(ThisRow,2) but it wasn't working. Can you guide me?

CKPORTE

 

[bensonsearch]

you have to qoute it so eg

Msg = "Hi there" & " " & Cells(ThisRow, 2) & vbCrLf & Cells(ThisRow, 1) & _
vbCrLf & Cells(ThisRow, 9) & vbCrLf & _
Cells(ThisRow, 6) & vbCrLf & Cells(ThisRow, 9)

if the "hi there" is a variable then simply writing it would be fine eg:

myvar = "Hi there"
Msg = myvar & " " & Cells(ThisRow, 2) & vbCrLf & Cells(ThisRow, 1) & _
vbCrLf & Cells(ThisRow, 9) & vbCrLf & _
Cells(ThisRow, 6) & vbCrLf & Cells(ThisRow, 9)

 

[ckporte]

Thank you! You are so helpful..I appreciate it since I am very new to this. Could you answer another question. I keep adding questions..

1.
By looking below, can you tell me why my cc field isn't populating?
I'm not sure how to say..now populate the CC field with these other addresses.

2.
Is there a way to remove the comma and spaceband if the cell it is looking at doesn't contain an email address?
______________________

Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, CC As String, Subj As String
Dim Msg As String, URL As String
Dim ThisRow As Long
ThisRow = ActiveCell.Row
' Email addresses
Email = Cells(ThisRow, 9) & "," & " " & Cells(ThisRow, 10) _
& "," & " " & Cells(ThisRow, 12) & "," & " " & Cells(ThisRow, 13)
' CC addresses
CC = Cells(ThisRow, 14) & "," & " " & Cells(ThisRow, 15)
' Message subject
Subj = "Please send the following hardclose request item(s) to Name@company.com."
' Compose the message
Msg = "Request Item " & " " & Cells(ThisRow, 2) & " is Due" & " " & Cells(ThisRow, 6)
' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
End Sub

 

[bensonsearch]

Hi,
the CC variable isnt being called anywhere. the way I do emails is the below and I think works easyier to understand.

Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
 
    Dim user As Variant
 
    user = 'your email addresses
 
 
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = 'body of email
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = user
        .Subject = "Subject goes here"
        .body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing