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:
And here is my Break links macro:
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: