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
Bookmarks