View Full Version : VBA Help
Hello everyone,
I'm new to the forum and to writing VBA scripts. I have an excel report that I would like to save with a specific file name and I'm having trouble figuring it out. The raw data report's name is "ALT_12_2_2016.xlsx" (the date changes every Friday when the report is ran) I would like to add ADF to the file name so that it looks like the following: ALT_ADF_12_2_2016.xlsx. Any ideas? I've tried a couple different options, currently I have the following:
ActiveWorkbook.SaveAs fileName:="C:\SCMS Reports\"
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Any help would be greatly appreciated, thanks!
JD
leal72
12-05-2016, 02:41 PM
this should work for you
Sub ChangeWorkbookName()
Dim WbName As String
Dim ReName As String
WbName = ActiveWorkbook.Name
ReName = Replace(WbName, "ALT", "ALT_ADF", 1)
ActiveWorkbook.SaveAs Filename:="C:\SCMS Reports\" & ReName
End Sub
this should work for you
Sub ChangeWorkbookName()
Dim WbName As String
Dim ReName As String
WbName = ActiveWorkbook.Name
ReName = Replace(WbName, "ALT", "ALT_ADF", 1)
ActiveWorkbook.SaveAs Filename:="C:\SCMS Reports\" & ReName
End Sub
That worked perfect, thank you so much! I have another piece that I am trying to add to the macro. I would like to attach and send the report through outlook and e-mail it to a built in outlook distribution list. Everything seems to be working except for the distro list, it just adds it as plain text. See code below:
Sub ADF()
Dim wb As Workbook
Dim strFile As String, strDir As String, sendMail As String, toMail As String, ccMail As String, bccMail As String
strDir = Range("E3")
strFile = Dir(strDir & Range("G3"))
sendMail = Range("B3")
toMail = Range("H3")
ccMail = Range("I3")
bccMail = Range("J3")
Set wb = Workbooks.Open(fileName:=strDir & strFile, Local:=True)
ActiveWindow.FreezePanes = True
Columns("A:A").Select
Selection.NumberFormat = "0000000000"
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
ActiveWorkbook.Save
If (sendMail = "Yes") Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = toMail
.CC = ccMail
'Distro list is called Weekly ADF Report in cell J3
.BCC = bccMail
.Subject = "Weekly ADF Report"
.Body = "Weekly ADF Report Attached"
.Attachments.Add wb.FullName
.Display
End With
End If
End Sub
leal72
12-06-2016, 04:42 PM
http://chandoo.org/wp/2012/04/23/send-mails-using-excel-vba-and-outlook/
That might be useful for you.
Something I did notice is that OutApp, and OutMail variables do not have a data type assigned to them. Might be part of why its not working.
Cool, I'll take a look at that, thanks!
It's strange, it will actually add the distribution name in the BCC field but it's only text, it's not resolving the name as a distribution list in Outlook. Even if I try to use "Check Names" in Outlook, it does not recognize it as a distro.
Thanks again!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.