Results 1 to 5 of 5

Stop macro looking at personal book when not told to

Threaded View

  1. #1
    Registered User
    Join Date
    01-27-2012
    Location
    Durham
    MS-Off Ver
    Excel 2003
    Posts
    61

    Stop macro looking at personal book when not told to

    Hi

    I have a macro that emails individual tabs to the address states in each tabs' A1 cell

    Sub Email_All()
        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
    
    
    
        TempFilePath = Environ$("temp") & "\"
    
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
              FileExtStr = ".xlsm": FileFormatNum = 52
        End If
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
    
        For Each sh In ThisWorkbook.Worksheets
            If sh.Range("A1").Value Like "?*@?*.?*" Then
    
                sh.Copy
                Set wb = ActiveWorkbook
    
                TempFileName = "Sheet " & sh.Name & " of " _
                             & ThisWorkbook.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>" & UF_Email.Txt_Name.Text & "<br><br>" & UF_Email.Txt_Title & "<br><br>" & UF_Email.Txt_Tel
                                           
                        .Attachments.Add wb.FullName
                    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
    The issue I have is that I want to use this macro from my personal workbook. But when I try, the macro looks for the email address in A1 of my personal macro book, rather than the workbook I want to send out. The code doesnt say 'look at my personal macro book', but as it is constantly creating new temp files im not sure where to start. How can I stop the macro looking at my personal book?

    Thank You
    Last edited by cooket4; 02-03-2012 at 10:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1