-
Solved: Generalize code
[VBA]Sub Test()
Windows("Test.xls").Activate
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
End Sub
Sub Test2()
Dim FName As String
Dim FPath As String
Sheets("Sheet1").Range("A3:G80").CopyPicture Appearance:=xlScreen, Format:=xlPicture
With CreateObject("Word.Document")
.Application.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False
FPath = "C:\Users\KL\Desktop"
FName = Sheets("Sheet1").Range("A6").Text
.SaveAs2 Filename:=FPath & "\" & FName
.Close 0
End With
End Sub
Sub FaxTest()
Const FILE_ATTACH As String = _
"C:\Users\klartigue\Desktop\IS THERE A WAY TO REFERENCE THE FPATH AND FNAME ABOVE??.docx"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim Lastrow As Long
Dim bodyText As String
Dim i As Long
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True
Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add("????")
oRecipient.Type = 1
.Subject = "Test"
If Dir(FILE_ATTACH, vbNormal) <> "" Then
.Attachments.Add ("C:\Users\KL\Desktop\IS THERE A WAY TO REFERENCE THE FPATH AND FNAME ABOVE??.docx")
.Save
End If
End With
End Sub[/VBA]
In the first part of the above code, I select sheet1. In the second part of the above code, I copy range (A3:G80) from sheet1 and paste it into a new word document. Then to name the word document, I reference the name in sheet1 on the excel sheet in cell A6.
For the third part of the code, I would like to fax the document out. As you can see in the above code where you choose the document to fax, is there a way to instead of stating the name "C:\Users\klartigue\Desktop\??.docx can you have this path reference cell A6 from sheet1 on the excel sheet? Is that possible?
-
You could pass the string as an argument.
Change FaxTest as follows:
Remove the line beginning:
Const FILE_ATTACH As String =
Change:
Sub FaxTest()
to:
Sub FaxTest(FILE_ATTACH As String)
Now include the string when you call FaxTest; eg. if you call it from Test2, just before End Sub, put:
FaxTest FPath & "\" & FName
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
[vba]
Dim FILE_ATTACH as String
FILE_ATTACH = "C:\Users\KL\Desktop\" & Worksheets("Sheet1").Range("A6").Value2 & ".docx"[/vba]
-
That works great, thank you!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules