PDA

View Full Version : Solved: Generalize code



Lartk
01-15-2013, 10:37 AM
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

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?

p45cal
01-15-2013, 11:07 AM
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

Kenneth Hobs
01-15-2013, 12:54 PM
Dim FILE_ATTACH as String
FILE_ATTACH = "C:\Users\KL\Desktop\" & Worksheets("Sheet1").Range("A6").Value2 & ".docx"

Lartk
01-15-2013, 01:14 PM
That works great, thank you!