Send a fax with preset number

[Jérémie Gent]

Hi!

I've got a worksheet containing an invoice with client information including
fax number.
I would like this invoice to be faxed when the user clicks on a button.
This works fine with the 'Printout' method of the Workbook (I can select the
fax as printer and the assistant shows up, which I'm ok with).
However, I would like the fax number to be automatically set in the
assistant window, so that the user doesnt have to type it in manually.
Is there a way to do this without buying a third party component?

I'm using Excel 2000.

Thanks in advance!
Jérémie

 

[Arvi Laanemets]

Hi

My advice is to use Word Mail Merge with Excel workbook as source.

In this workbook, you must have a table, where all data needed for invoice
are stored on single row (It can be one you use for data input, or it can be
calculated automatically from input sheet). This table must contain also
various customer info (customer name, address, mail address, fax number,
etc.). The table looks like this:
InvoiceNr, Date, Customer, Country, State, District, Address, Mail, Fax,
Item1, Amount1, Price1, Sum1, Item2, ..., PriceN, SumN, TotalAmount,
TotalSum, ...
NB! Better keep this table on first (leftmost) sheet in workbok - otherwise
it'll be difficult to use it as MM source.

Now you create a mail merge document in Word with Invoices table in Excel
workbook as source. (You can enter table column names as fields into mail
merge document). NB! Now you can use all Word features to design the
invoice.

In Mail Merge options, you can select the output device (to file, to
printer, to mail, or to fax). Whe you select mail or fax as output device,
you also can determine there field, where mail address or fax number is
stored.

Before processing the mail merge, you can set the filter. Only rows with
filter condition TRUE from Invoices table are processed.

 

[Jérémie Gent]

Thank you so much for this long and clear answer!
I'll think of it and will see if I can use it for my case (which is actually
meant to be an Excel Application, and all of the users may unfortunatly not
have Word).
However thanks a lot, I think your answer also means : there doesn't seem to
be any solution for doing that directly from Excel, am I right ?

Jérémie

 

[Arvi Laanemets]

Hi

Probably it's possible to write a macro for this.
You enter all customers and their fax numbers into table on separate sheet.
On invoice sheet, you have a command button. When clicked on, the button
starts the macro, which reads customer name/ID from Invoice sheet, finds
according fax number from Customers table, and starts Printout method with
his fax number as one of parameters (I haven't used this method myself, so
I'm not sure about syntax at all - it's only an idea).

 

[Jérémie Gent]

Thanks Arvi, that's exactly what I'm trying to do, are you sure of what you
wrote ?
Can you use the printout method with the fax number as parameter ?
In excel 2000 ?

That would be just fantastic, but I couldn't find information on that
anywhere, so if you think that's doable, please tell me how!

thanks!
Jérémie

 

[Arvi Laanemets]

Hi

I had a look at PrintOut method syntax - it looks like there really is no
direct way for this. The method itself doesn't support sending faxes.
Have you tried to send a fax manually and record the process as macro. Maybe
you get some idea from recorded code?

 

[Jérémie Gent]

Hi Arvi!

I finally found an object working fine and it is free (works on WinXP/Off2K)
: Faxcomlib.faxserver
So I think I can solve my problem with this.
Thanks again for your help
Jérémie

 

[Jérémie Gent]

Well Arvi, after a lot of time involved in searching for other solutions, I
finally chose another one.
It is for sure not the safest, but it my case it works fine.

Here is my function:

Public Sub FaxeFeuille(FeuilleAFaxer As String, destinataire As String,
numero As String)
Dim keys As String
Worksheets(FeuilleAFaxer).Select
keys = "^p%nfax~~~" & destinataire & "{TAB}{TAB}" & numero
SendKeys keys
End Sub

You must have a fax printer called 'Fax'.
Works on Excel 2K / Windows Xp german.

BR
Jérémie