Alignment in VBA?

[Valeriya]

Hi Guys!
I’ve wrote a VBA code to send the contents of record set as an email from MS Access.
However, my output just ugly. I’m afraid nobody will understand my message.
Is anybody know if there is an Alignment function in VBA I can use to format columns in the Outlook email?
Here is my code

 Option Compare Database


Private Sub Customer_Info_Update()

Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(" SELECT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type, [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber, IIf(Replace([dbo_MKT_CustomerReps].[CustName],' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[AccountName], 0),' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[CustName],[dbo_MKT_CustomerReps].[CustName]) AS NewAccountName, [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountName " & _
" FROM [dbo_MKT_CustomerReps] LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON [dbo_MKT_CustomerReps].CustNbr = [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber " & _
" WHERE ((([0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber) Is Not Null) AND ((IIf(Replace([dbo_MKT_CustomerReps].[CustName],' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[AccountName], 0), ' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[CustName],[dbo_MKT_CustomerReps].[CustName])) Like 'Will be Updated*'));")

 
Set rs1 = CurrentDb.OpenRecordset(" SELECT DISTINCT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type, [0000 CONS ACCOUNT, RVP, DAM, REGION].RVP, IIf([dbo_MKT_CustomerReps].[RSD_Name]<>[0000 CONS ACCOUNT, RVP, DAM, REGION].[RVP],'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[RSD_Name],[dbo_MKT_CustomerReps].[RSD_Name]) AS newRVP" & _
" FROM dbo_MKT_CustomerReps LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON dbo_MKT_CustomerReps.CustNbr = [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber" & _
" WHERE (((IIf([dbo_MKT_CustomerReps].[RSD_Name]<>[0000 CONS ACCOUNT, RVP, DAM, REGION].[RVP],'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[RSD_Name],[dbo_MKT_CustomerReps].[RSD_Name])) Like 'Will be Updated*'));")

i = 1

Do Until rs.EOF 'first loop


strMessage = strMessage & "Account#" & rs![AccountNumber] & "      " & rs![AccountName] & "       " & rs![NewAccountName] & vbCrLf


rs.MoveNext
i = i + 1
Loop

rs.Close: Set rs = Nothing

j = 1

Do Until rs1.EOF 'second loop

strMessage1 = strMessage1 & rs1![RVP] & "       " & rs1![newRVP] & vbCrLf
rs1.MoveNext
j = j + 1
Loop
rs1.Close: Set rs1 = Nothing


[b]DoCmd.SendObject acSendNoObject, , , "To@toDot.com", , , "Results", "Update Account name to:" & Chr(13) & strMessage & vbCrl & Chr(13) & "Update RVP name to:" & Chr(13) & strMessage1, True [/b]


End Sub

Private Sub Customer_Info_Update_1()
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset


Set rs2 = CurrentDb.OpenRecordset(" SELECT DISTINCT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type, [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber, IIf(Replace([dbo_MKT_CustomerReps].[DAM_Desc], ' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[DAMName],0),' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[DAM_Desc],[dbo_MKT_CustomerReps].[DAM_Desc]) AS NewDAMName, [0000 CONS ACCOUNT, RVP, DAM, REGION].DAMName" & _
" FROM dbo_MKT_CustomerReps LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON dbo_MKT_CustomerReps.CustNbr = [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber" & _
" WHERE ((([0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber) Is Not Null) AND ((IIf(Replace([dbo_MKT_CustomerReps].[DAM_Desc],' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[DAMName],0),' ', ''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[DAM_Desc],[dbo_MKT_CustomerReps].[DAM_Desc])) Like 'Will be Updated*'));")


Set rs3 = CurrentDb.OpenRecordset(" SELECT DISTINCT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type, [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber, IIf(Replace([dbo_MKT_CustomerReps].[DAM_Name], ' ', '')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[DAMMgr],0), ' ',''),' Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[DAM_Name],[dbo_MKT_CustomerReps].[DAM_Name]) AS NewDAMMgr, [0000 CONS ACCOUNT, RVP, DAM, REGION].DAMMgr" & _
" FROM dbo_MKT_CustomerReps LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON dbo_MKT_CustomerReps.CustNbr = [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber" & _
" WHERE ((([0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber) Is Not Null) AND ((IIf(Replace([dbo_MKT_CustomerReps].[DAM_Name], ' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[DAMMgr]),' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[DAM_Name],[dbo_MKT_CustomerReps].[DAM_Name])) Like 'Will be Updated*'));")


Set rs4 = CurrentDb.OpenRecordset(" SELECT DISTINCT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type, [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber, IIf(Replace([dbo_MKT_CustomerReps].[REP_Name],' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[REP],0),' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[REP_Name] ,[dbo_MKT_CustomerReps].[REP_Name]) AS NewREP, [0000 CONS ACCOUNT, RVP, DAM, REGION].REP" & _
" FROM dbo_MKT_CustomerReps LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON dbo_MKT_CustomerReps.CustNbr = [0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber" & _
" WHERE ((([0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber) Is Not Null) AND ((IIf(Replace([dbo_MKT_CustomerReps].[REP_Name],' ','')<>Replace(Nz([0000 CONS ACCOUNT, RVP, DAM, REGION].[REP],0),' ',''),'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[REP_Name],[dbo_MKT_CustomerReps].[REP_Name])) Like 'Will be Updated*'));")


k = 1

Do Until rs2.EOF 'First Loop

strMessage2 = strMessage2 & " Account# " & rs2![AccountNumber] & "      " & rs2![DAMName] & "       " & rs2![newDAMName] & vbCrLf
rs2.MoveNext
k = k + 1
Loop
rs2.Close: Set rs2 = Nothing


l = 1

Do Until rs3.EOF 'Second Loop

strMessage3 = strMessage3 & " Account# " & rs3![AccountNumber] & "      " & rs3![DAMMgr] & "        " & rs3![newDAMMgr] & vbCrLf
rs3.MoveNext
l = l + 1
Loop
rs3.Close: Set rs3 = Nothing

m = 1

Do Until rs4.EOF
strMessage4 = strMessage4 & "Account#" & rs4![AccountNumber] & "     " & rs4![REP] & "       " & rs4![newREP] & vbCrLf
rs4.MoveNext
m = m + 1
Loop

rs4.Close: Set rs4 = Nothing

[b]DoCmd.SendObject acSendNoObject, , , "To@toDot.com” , , "Results", "Update DAMName to:" & Chr(13) & strMessage2 & vbCrl & Chr(13) & "Update DAMMgr to:" & Chr(13) & strMessage3 & vbCrl & Chr(13) & " Update REP to: " & vbCrl & Chr(13) & strMessage4, True[/b]

End Sub


Public Function get_Customer_Info_Update() As Variant
    Customer_Info_Update
End Function

Public Function get_Customer_Info_Update_1()
    Customer_Info_Update_1
End Function

Here the example of output:

Update Account name to:
Account#249231 MUKT MUSSER Will be Updated to MIRKIN, JOHN
Account#5037 THOMAS ALLA Will be Updated to KALIEVA; ANARA
Account#7024 PODKIN, BRAD (DO NOT CHANGE PER A/PDEPT) Will be Updated to PAUL, MCCONNEL
Account#256879 KENNY, SASHA Will be Updated to KENNY, PASHA(DO NOT CHANGE)

Update RVP name to:
KEVIN SASHA Will be Updated to OPEN
OPEN SOUTHEAST Will be Updated to JEFF KULIEVF
OPEN SOUTHEAST Will be Updated to JEFF KULIEV

Thanks in advance!

Valeriya

 

[mp9]

Well you could look at using something like vbTab (in the same way you've used vbCrLf) to help structure your string as they are created, e.g.

strMessage2 = strMessage2 & " Account# " & rs2![AccountNumber] & vbTab & rs2![DAMName] & vbTab & rs2![newDAMName] & vbCrLf

This may or may not work! Alternatively you could do away with using SendObject to write the mail, and use something like the alternative at

http://www22.brinkster.com/accessory/modules/005.shtml

but that will introduce the problem of having to code around the Outlook object model guard (assuming your email client is Outlook).

 

[CautionMP]

Another solution would be to send the data as an HTML table.

When you gather the information from the recordsets you would need to wrap you data in the appropriate HTML tags, then make a small change to the routine provided by the link posted in [navy]mp9[/navy]'s response.

...
If Not IsMissing(MessageText) Then
   [green]'[s].Body = MessageText[/s][/green]
   .[b]HTMLBody[/b] = MessageText
End If
...

Hope this helps,
CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[Valeriya]

MP9 and CautionMP,

Thanks a million for your tips and a VERY useful link!

I hope that will help,
Thanks a lot again,
Valeriya