+ Reply to Thread
Results 1 to 7 of 7

After running an email macro other macros will not work.

Hybrid View

  1. #1
    Registered User
    Join Date
    12-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    5

    After running an email macro other macros will not work.

    Greetings,

    I am new to VBA and macros so there might be simple fix to this problem. I have a macro that creates an email from the active sheet. Everything works fine and the macro performs flawlessly, however as soon as it is finished none of the other macros or objects (checkboxes) on the sheet work until you click in a cell. Then everything seems to reset and work fine again. I would like people to be able to email the sheet and then come right back to it and be able to use the print button without having to click anywhere else.
    I did try to set the focus at the end back to one of the cells but that didn't seem to work either.

    Here is the code for reference:
    Private Sub Email_Utility()
    
        Dim oApp As Object
        Dim oMail As Object
        Dim LWorkbook As Workbook
        Dim LFileName As String
        Dim cNNumber As String
        Dim cSerial As String
        Dim cFrmDate As String
        Dim htmlBody As String
        Dim ws As Worksheet
            
        ActiveSheet.Name = Range("AE50").Value
        
        cNNumber = Cells(3, "AB").Value
        cSerial = Cells(3, "AL").Value
        cFrmDate = Cells(2, "AL").Value
            
        'Turn off screen updating
        Application.ScreenUpdating = False
    
        'Copy the active worksheet and save to a temporary workbook
        ActiveSheet.Copy
        Set LWorkbook = ActiveWorkbook
    
        'Create a temporary file in your current directory that uses the name
        ' of the sheet as the filename
        LFileName = LWorkbook.Worksheets(1).Name
        On Error Resume Next
        'Delete the file if it already exists
        Kill LFileName
        On Error GoTo 0
        'Save temporary file
        LWorkbook.SaveAs Filename:=LFileName
    
        'Create an Outlook object and new mail message
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        
        
        With oMail
            .To = "[email protected]"
            .Subject = "RTS Flight Request (" & cNNumber & "/" & cSerial & ") " & cFrmDate
            .htmlBody = "See attached 'Pending' RTS Flight<br><br>"
            .htmlBody = .htmlBody & "<span style='background:yellow'>Estimated maintenance completion date: ______</span><br><br>"
            .htmlBody = .htmlBody & "Thank you!"
            .Attachments.Add LWorkbook.FullName
            .Display
        End With
    
        'Delete the temporary file and close temporary Workbook
        LWorkbook.ChangeFileAccess Mode:=xlReadOnly
        Kill LWorkbook.FullName
        LWorkbook.Close SaveChanges:=False
    
        'Turn back on screen updating
        Application.ScreenUpdating = True
        Set oMail = Nothing
        Set oApp = Nothing
         
        Set ws = ActiveSheet
        
      ws.Range("AL2").Select
            
        
    End Sub
    Any help is greatly appreciated. Thanks!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: After running an email macro other macros will not work.

    Hi,

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: After running an email macro other macros will not work.

    Understood, I will be sure to attach in future. Thanks for the info

    See attached.
    Attached Files Attached Files
    Last edited by dynamicexcel; 12-28-2012 at 02:44 PM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: After running an email macro other macros will not work.

    Hi,

    I'm struggling to replicate your apparent problem.

    Out of the box on my laptop I can click the eMail to QC button which creates the email. Jumping back into Excel I can immediately click the Print button or select any of the checkbox objects without any problem.

    If I add the line 'Range("AL2").Select' as the last instruction then AL2 is made the active cell.

    Are you saying that after clicking the eMail button you then can't click the Print button?

  5. #5
    Registered User
    Join Date
    12-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: After running an email macro other macros will not work.

    Hi,

    I tried it again and it still behaves the same as before. The one difference in reproducing the problem might be that the sheet I uploaded is unprotected. When the sheet is deployed it will be protected with only allowing users to select unlocked cells. I have been running it with protection. Thanks so much for your response.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: After running an email macro other macros will not work.

    Hi,

    I've just protected the workbook to test this and still don't encounter any problems.

    Which version of Excel are you using? Your profile says 2012 although I'm not aware there is such a version. The latest production versions are Excel for Windows 2010 (2013 has still not been released) and Excel for Mac is 2011.

  7. #7
    Registered User
    Join Date
    12-28-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: After running an email macro other macros will not work.

    My apologies, I am using Excel 2010.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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