Excel/Word VB Coding question

[Stuart_C]

I'm looking to take a list of staff and create their E-mail signatures (becuase they can't be trusted to do it themselves) and I'm looking for some ideas.

 

The signature files are saved as 2 text files where just the variables such as phone number, name and job title would change.

 

now I could create all this as a mail merge but I would need to save each file individually (which would be the issue). Alternatively if could output direct from excel into the text file this would be fine.

 

But I'm at a bit of a loss about the best way to create the output. Is there a way to use the VB script to acomplish this task.

 

If it makes more sense I would start with a list

 

Person1 Job1 Phone1

Person2 Job2 Phone2

 

And export the text

Person1

Job1

Phone1

To a file .txt

[Steve21]

Do you actually want this in word/excel? Or is it just pure txt files and vbscript?

 

Steve

[Steve21]

For a really lazy way - Macro based excel

 

Example data:

 

Macro:

 

Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim stream As TextStream

For i = 1 To 10 Step 1

Set stream = fso.CreateTextFile("C:\test\" & Worksheets("Sheet1").Cells(i, "A").Value & ".txt", True)
stream.WriteLine Worksheets("Sheet1").Cells(i, "A").Value
stream.WriteLine Worksheets("Sheet1").Cells(i, "B").Value
stream.WriteLine Worksheets("Sheet1").Cells(i, "C").Value
stream.WriteLine Worksheets("Sheet1").Cells(1, "F").Value
stream.Close

Next

 

Outcome:

 

Any use?

 

Steve

[MatthewL]

There is software that runs on your exchange box that can pull details from AD so it will always be up to date providing AD is and only has to be changed in one place.

 

This is it.

[Stuart_C]

@Steve21 I'm outputting to a text file, once I save the files in the correct location these are read by the mail server's web interface. That's probably enough to get me started and working. As I'll have everyone's job titles and details in a excel list it's the place I was looking to start from. It's a bit of a pet project so I'll give it a go when I get time, hopefully in the next week or so.

@MatthewL Cheers for the suggestion but I'm not using Exchange.

[Stuart_C]

So thanks to @Steve21 that has worked fantastically for part 1 but I'm struggling with part 2.

I've used the above commands to create a pure text file, but I need write another that creates a text file with some HTML in.

So I'm trying th fit the line (for example)

in one of the stream.writeline commands but it doesn't work.

 

I know it's going horribly wrong becuase of the use of speach marks for starters but I was hoping someone had some advice on how to write this into a line on a text file.

[LosOjos]

So thanks to @Steve21 that has worked fantastically for part 1 but I'm struggling with part 2.

I've used the above commands to create a pure text file, but I need write another that creates a text file with some HTML in.

So I'm trying th fit the line (for example)

in one of the stream.writeline commands but it doesn't work.

 

I know it's going horribly wrong becuase of the use of speach marks for starters but I was hoping someone had some advice on how to write this into a line on a text file.

 

You need to escape your speech marks by doubling them up, like so:

 

 

It'll only write a single quote to the output, but you have to escape them like this so the compiler knows it's not the end of a string

[Stuart_C]

That looks good! Cheers

Just need to do a load of copy and pasting and then test.

Thanks.