Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mail Merge 1

Status
Not open for further replies.
SonJ

SonJ

Programmer
Oct 24, 2002
166
GB
Hi,

I have a requirement for an application that I am building to enable users to print address labels. I think the preferred solution would be to use the mail merge function in word (as a formatted template for the label size is readily available), once I produce the spreadsheet of the data in excel.

Does anyone know if it is possible to do this from a web application? If so, does anyone have any pointers? If not, is there a simple alternative.

Any help would be much appreciated.

 
Sort by date Sort by votes
Please clarify the role of Excel in this project.

Are you saying that an Excel file is the data source?
 
Upvote 0 Downvote
Hi Sheco,

Basically I want to do a mail merge of labels from my web application. I am not sure if it will be easier to do this in word or excel, though looking at various posts, word seems to be the way forward. Any help would be much appreciated.
 
Upvote 0 Downvote
Ah, so the addresses themselves are stored in a database and you are just considering Word and Execl as potential output formats? Is that right?
 
Upvote 0 Downvote
here...something like this:

Code: 
<html>
<head>
<title>Labels</title>
<style>

<!--
div.Section1
	{page:Section1;}
p.MsoNormal
	{mso-style-parent:"";
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";
	margin-left:0in; margin-right:0in; margin-top:0in}

p.DotNetGnat { page-break-before: always }
-->

</style>
<meta name="blah" content="Labels">
</head>

<%
'put whatever response.contenttype you want...word or excel

'declare any variables

Set rsObj = Server.CreateObject("ADODB.Recordset")
Set MdConnection = Server.CreateObject("ADODB.Connection")
MdConnection.ConnectionString = "your connection string"
MdConnection.Open

sql = "your query"

rsObj.Open sql, MdConnection

If rsObj.EOF AND rsObj.BOF Then 

Response.Write "Sorry. No records returned for your search criteria"

Else


%>
<body>

<div class="Section1">

<table border="0" cellspacing="0" cellpadding="0" style="border-collapse: collapse" height="181">

<%
  count = 0
  myMod = 0
 


   do while not rsObj.EOF
    myMod = count mod 3


    if (count) MOD 30 = 0 then %>
</table>
  <p class='DotNetGnat' align='center'> </p> 

<table border="0" cellspacing="0" cellpadding="0" style="border-collapse: collapse" height="181">

<%End if

    'Do we need to start a new row?
    
     
     if (myMod = 0) then Response.Write "<tr style=""height: 1.0in"">"

    
    
     'Write table data cell here

    Response.Write "<td width=""257"" style=""width: 192.75pt; height: 1.0in; padding-left: .75pt; padding-right:.75pt;    padding-top: 0in; padding-bottom: 0in"">"
 
Response.Write "<p class=""MsoNormal"" align=""center"" style=""margin-top:0in;margin-right:5.3pt;margin-bottom:0in;margin-left:5.3pt;margin-bottom:.0001pt;text-align:center"">"

Response.Write"<b><span style=""font-family: Arial"">" & rsObj("whatever")&"</span></b></p>"

Response.Write "<p class=""MsoNormal"" align=""center"" style=""margin-top:0in;margin-right:5.3pt;margin-bottom:0in;margin-left:5.3pt;margin-bottom:.0001pt;text-align:center"">"
Response.Write "<span style=""font-family: Arial""> " & rsObj("whatever")&"</span></p>"

Response.Write "<p class=""MsoNormal"" align=""center"" style=""margin-top:0in;margin-right:5.3pt;margin-bottom:0in;margin-left:5.3pt;margin-bottom:.0001pt;text-align:center"">&nbsp;</p>"

Response.Write "<p class=""MsoNormal"" align=""center"" style=""margin-top:0in;margin-right:5.3pt;margin-bottom:0in;margin-left:5.3pt;margin-bottom:.0001pt;text-align:center"">&nbsp;</td>"  


'Do we need to end a the table row?
    if (myMod = 2) then Response.Write "</tr>"

    count = count + 1

    rsObj.MoveNext
  
  loop

'finish the last table row here
  select case myMod
    case 0
      'need 2 empty cells and close table row
      Response.Write "<td width=""257"" style=""width: 192.75pt; height: 1.0in; padding-left: .75pt; padding-right:.75pt; padding-top: 0in; padding-bottom: 0in"">&nbsp;</td><td width=""257"" style=""width: 192.75pt; height: 1.0in; padding-left: .75pt; padding-right:.75pt; padding-top: 0in; padding-bottom: 0in"">&nbsp;</td></tr>"    
    case 1
      'need 1 empty cell and close table row
      Response.Write "<td width=""257"" style=""width: 192.75pt; height: 1.0in; padding-left: .75pt; padding-right:.75pt; padding-top: 0in; padding-bottom: 0in"">&nbsp;</td></tr>"
    case 2
      'nothing else needed
  end select
%>
</table>

<%
 
rsObj.Close
End if
%>
</body>
</html>

hope this helps...

-DNG
 
Upvote 0 Downvote
If at all possible do it with a plain web page like DNG suggested. Using Office Automation on a web server is problematic... it is too easy to end up with "phantom" instances of excel or winword floating around in your web server's memory chewing up the available memory.
 
Upvote 0 Downvote
DNG,

Thanks for the suggested solution, I think this is defo the way forward as I have been working with mail merge for the last few days and not got any further in finding a solution.

I have a question though. The results vary depending on the printer that I use ... odd, I know. It works really well when I use a network printer and not so well if I use a personal printer. Is there anyway I can control the left, right, top and bottom margins to ensure consistent printing?

SonJ
 
Upvote 0 Downvote
SonJ,

Yes you can set the printer settings...let me know if you need the help with the code...but you need to do that inside the <style> tags...

-DNG

 
Upvote 0 Downvote
Thanks DNG. I have not done that before (and not too sure where I should start!), so if you could let me know what I need to do that would be great! Also, do you have any references for this?

Your help is much appreciated.

SonJ
 
Upvote 0 Downvote
DGN, what are you using to format the printout? I've not had much success. IE has whacko printing margins, and I've never been able to get the margins close enough to the edge when creating a word file.
 
Upvote 0 Downvote
travis,

do you see this code in the style tags

Code: 
<style>

<!--
div.Section1
    {page:Section1;}
p.MsoNormal
    {mso-style-parent:"";
    margin-bottom:.0001pt;
    font-size:12.0pt;
    font-family:"Times New Roman";
    margin-left:0in; margin-right:0in; margin-top:0in}

p.DotNetGnat { page-break-before: always }
-->

</style>

this is where we can change the formatting...

i am not sure if this what actual problem you are facing...so please post more details...may be you can start a new thread so that others might also assist you...

-DNG
 
Upvote 0 Downvote
You may need to config the page set-up using IE options. You will only have to do this once for the label output page.
 
Upvote 0 Downvote
Thanks DGN. Yeah, I think i've done as much as I can with css. I was wondering if you knew any secrets. I was reading up on it, and the problem lies with IE. SonJ is right; the user has to manually config IE margins.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to embed image in e-mail ?
Replies
9
Views
177
ChrisHirst
ChrisHirst
clmp
  • Locked
  • Question
Mailto with subject and body text not working in ASP document
Replies
3
Views
119
ChrisHirst
ChrisHirst
thompom
  • Locked
  • Question
merge multidimensional array func
Replies
1
Views
60
ChrisHirst
ChrisHirst
sdagger2
  • Locked
  • Question
ASP mail not working in chrome?
Replies
6
Views
86
sdagger2
sdagger2
craigward
  • Locked
  • Question
Accessing Web Mail
Replies
1
Views
31
emozley
emozley

Part and Inventory Search

Sponsor

Back
Top