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)

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

Try this

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" <(E-Mail Removed)> wrote in message news:477f7c87-628e-47e6-bad6-(E-Mail Removed)...
> 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)

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

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" <(E-Mail Removed)> wrote in message news:6e692d7d-4080-4d51-a1d3-(E-Mail Removed)...
> 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
>

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" <(E-Mail Removed)> wrote in message news:c8684d93-4228-47d5-8dcb-(E-Mail Removed)...
> The situation I had in mind Ron, is where my TO; CC and BCC addresses
> are all in the same column
>

Wow Thanks Ron, its a simple as that

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)

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 Removed)","")

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" <(E-Mail Removed)> wrote in message news:dedca8e1-091d-4c87-ad5f-(E-Mail Removed)...
> 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 Removed)","")
>