Macro that runs for every sheet stops working

Hi

I have a macro that runs through every sheet (no matter how many or few) of a workbook. It attaches each relevent worksheet to the relevent email within the sheet.

This macro worked fine, up untill a few months ago. The macro runs and attaches most sheets as normal, but it the email address in cell A1 which it looks up is no longer recognised

The bit of the code is

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Range("A1").Value Like "?*@?*.?*" Then

I have looked into this and cannot find out to why the email addresses are not picked up, each is a valid address with @ in and all vary in length.

Why would the macor work as normal and then not recognise the email addresses? No error messege is returned, the macro just runs till its normal end.

Thank You

Thanks. Ill try that

I have gone throught the 'step into' mode and can actually see that its skipping the step in question, and the @ sign is physically there. This is what I dont understand, its there so why is it not being opicked up?

The cell in Excel is formatted as Genaral, which is the same as the others that have worked. All sheets are exactly the same, as they come from a template. The email address is in cell A1 on every sheet to, but the macro only recognises the @ for a handfull of pages. The latest workbook was 96 sheets, but 44 were only picked up. I have ran this macro months ago on workbooks with over 100 sheest and worked fine.

If this help the full code is:

Sub Email_All()
'

' Macro created 24/01/2012 by me
'

'Working in 2000-2010
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim SigString As String
    Dim Signature As String
    


    SigString = "C:\Documents and Settings\" & Environ("username") & _
    "\Application Data\Microsoft\Signatures\"
    SigString = SigString & Dir(SigString & "*.htm")
    TempFilePath = Environ$("temp") & "\"
    
    
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2010
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    With Application
        .ScreenUpdating = True
        .EnableEvents = False
    End With

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Range("A1").Value Like "?*@?*.?*" Then

            sh.Copy
            Set wb = ActiveWorkbook

            TempFileName = "Sheet " & sh.Name '& " of "_
                         '& ActiveWorkbook.Name & " " _
                         '& Format(Now, "dd-mmm-yy h-mm-ss")

            Set OutMail = OutApp.CreateItem(0)
            With wb
                .SaveAs TempFilePath & TempFileName & FileExtStr, _
                        FileFormat:=FileFormatNum
                On Error Resume Next
                With OutMail
                    .To = sh.Range("A1").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = UF_Email.Txt_Subject.Text
                    .htmlBody = UF_Email.Txt_Intro & "<br><br>" & UF_Email.TxtText & "<br><br>" & Signature
                    
                    
                    .Attachments.Add wb.FullName
                    'You can add other files also like this
                    '.Attachments.Add ("C:\test.txt")
                    .display   ' .Display or .send
                End With
                On Error GoTo 0
                .Close SaveChanges:=False
            End With
            Set OutMail = Nothing

            Kill TempFilePath & TempFileName & FileExtStr
        End If
    Next sh

    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    UF_Email.Hide
End Sub

Ill try it, but wont be able to till later in the month, as its busy at work with the year end audit. Ill let you know once I try it

Thanks