VBA Help

[JDW]

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]

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

[JDW]

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]

http://chandoo.org/wp/2012/04/23/sen...a-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.

[JDW]

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!