CC Field Q

[Sean]

Below is the code (ex Ron de Bruin) I am using to populate the CC
field in my auto e-mail file. The issue I am trying to overcome is
sometimes the field where the CC values are held are empty, other
occasions they have a valid address, how could I tweak below to allow
this?

For Each cell In ThisWorkbook.Sheets("Mail") _
.Columns("AI").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strcc = strcc & cell.Value & ";"
End If
Next
strcc = Left(strcc, Len(strcc) - 1)

 

[Ron de Bruin]

strcc = Left(strcc, Len(strcc) - 1)

Try this

If Len(strcc) > 0 Then strcc = Left(strcc, Len(strcc) - 1)

 

[Sean]

Thanks Ron.

One other small issue, how do I limit the range where it looks for an
address in Column AI, to say AI3:AI5, rather than looking n the whole
column

 

[Ron de Bruin]

It will only loop through the cells with data in the column

.Columns("AI").Cells.SpecialCells(xlCellTypeConstants)

 

[Sean]

The situation I had in mind Ron, is where my TO; CC and BCC addresses
are all in the same column

 

[Ron de Bruin]

..Range("A3:AI5").Cells.SpecialCells(xlCellTypeConstants)

 

[Sean]

Wow Thanks Ron, its a simple as that

 

[Sean]

Ron I tried If Len(strcc) > 0 Then strcc = Left(strcc, Len(strcc) -
1)

but getting a message 'no cells found' on line

For Each cell In ThisWorkbook.Sheets("Area 1") _
.Range("AI2:AI3").Cells.SpecialCells(xlCellTypeConstants)

Which is correct in that there is no address, but thats were I wish to
have no e-mail address added to my mail (my TO field will always have
an address, so it will be a vaild e-mail, just my CC and BCC feilds
may not always have an address)

 

[Sean]

Ron, it maybe relevant but the e-mail addresses in the CC and BCC
feild are formula derived, as like

=IF(AY$10>=1,"(e-mail address removed)","")

 

[Ron de Bruin]

Hi Sean

Use this then

On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Mail") _
.Range("A3:AI5").Cells.SpecialCells(xlCellTypeFormulas)

If cell.Value Like "?*@?*.?*" Then
strcc = strcc & cell.Value & ";"
End If
Next
If Len(strcc) > 0 Then strcc = Left(strcc, Len(strcc) - 1)
On Error GoTo 0

 

[Sean]

Thanks Ron, seems to work great