How to combine: Mail loop with break links

Duke Ex Machina

New Member
Joined
Jan 31, 2017
Messages
1
Hey all, I am trying to create a macro that will save different tabs of a single workbook into a new file and send that file off to the departmenr head and then go on to the next tab/department. I'm running into issues because the file is saved with links. I have to break the links in the new saved file and then send. Any help is appreciated, but how do I combine these to break the links before I save, send and go to the next department/tab?

Here is my mail loop macro:
Code:
Sub EmailLoop()


Dim EmailName As String
Dim TabName As String
Dim q As Integer
Dim NewFileName As String
Dim Path As String
Dim MasterFileName As String
Dim Department As String
Dim DateVariable As String
Dim OutApp As Object
Dim OutMail As Object
    
'Set default values for variables
    q = 1 'loop variable
    Path = ActiveWorkbook.Path
    MasterFileName = ActiveWorkbook.Name


'Set first run values for variables
    Sheets("Macro").Select
    
    DateVariable = Format(Range("m1").Value, "yyyy-mm")
    
    Range("l3").Select
    
    ActiveCell.Offset(q, 0).Select
    TabName = ActiveCell.Value
    
    ActiveCell.Offset(0, 1).Select
    EmailName = ActiveCell.Value
    
    ActiveCell.Offset(0, 1).Select
    Department = ActiveCell.Value


'Start Loop
    Do


'Set name and path fo the new file name
        NewFileName = Path & "" & "FedEx CrossBorder - Cost Detail - " & Department & " - " & DateVariable & ".xlsx"
          
'Copy tab
        Sheets(TabName).Copy
        
'Save and close file
        ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
        Sheets(TabName).Range("a1").Select
                       
        ActiveWorkbook.Save
        ActiveWindow.Close
        
        Workbooks(MasterFileName).Activate
        Sheets("Macro").Select
    
'Send Email
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        With OutMail
            .to = EmailName
            .CC = ""
            .BCC = ""
            .Subject = "Monthly Departmental Costs"
            .Body = "Attached are the rolling monthly costs for your department.  Please review the attached spreadsheet.  If you have any questions please email someone@somewhere.com" & vbNewLine & "Regards," & vbNewLine & "Business Intelligence Group"
            .Attachments.Add NewFileName
            .Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    
'Reset Loop
    q = q + 1
    
    
    Range("l3").Select
    
    ActiveCell.Offset(q, 0).Select
    TabName = ActiveCell.Value
    
    ActiveCell.Offset(0, 1).Select
    EmailName = ActiveCell.Value
    
    ActiveCell.Offset(0, 1).Select
    Department = ActiveCell.Value
    
    Loop Until TabName = "End"




End Sub

And here is my Break links macro:
Code:
Sub BreakLinks()
Dim Links As Variant
Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(Links)
ActiveWorkbook.BreakLink _
    Name:=Links(i), _
    Type:=xlLinkTypeExcelLinks
Next i
End Sub
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,215,025
Messages
6,122,734
Members
449,094
Latest member
dsharae57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top