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?
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?